Syntax/code to replace an observation with its related observation

[ts1993]

id            BLK

AF120     0.02

AF120R

BD111     0.5

BD111R  

in the above question I  want to replace the missing BLK observation with the BLK of the related id

what would be the syntax in sas 9.4?

e.g. BLK value for AF120R eq to that of AF120, 0.02, and so on.

[Reeza]

Will you always have the pattern of Data, Missing, Data, Missing? Will the related variable always have the same prefix except with an R at the end?

[ts1993]

No, the missing data does not have any pattern, every then and there

Yes, the related variable always have the same prefix except R or R1, R2, R3, etc. at the end, mostly just R

thank you

[Reeza]

Can you have R in the text as well?
It's easy to do for your example data but the solution won't scale to your actual data if we don't know the remaining details. I think you need to provide more sample data that better reflects your actual data.

[novinosrin]

data have;
input id $            BLK;
cards;
AF120     0.02
AF120R .
BD111     0.5
BD111R  .
;
proc sql;
create table want as
select a.id,b.blk
from have a left join (select * from have(where=(blk>.))) b
on find(a.id,strip(b.id))>0;
quit;

Safe bet

data have;
input id $            BLK;
cards;
AF120     0.02
AF120R .
BD111     0.5
BD111R  .
;
proc sql;
create table want as
select a.id,b.blk
from have a left join (select * from have(where=(blk>.))) b
on find(a.id,strip(b.id))>0 or find(b.id,strip(a.id))>0;
quit;

[Reeza]

And if you add the following IDs to the table? I don't think that solution will scale to the actual data.

BD11234
BD11234R2

[novinosrin]

True, that would deviate from the complete pattern . It's up to OP to clarify

I guess in that case, COMPGED, COMPLEV, COMPARE and other fuzzy merge, REgex may be the way forward

[Reeza]

I think you'd need to separate the R/R1/R2/R3 portion and merge on that, but I'm waiting on OP to clarify.

[ts1993]

Thank you for the codes

didn't quite work but I was able to mod it from there to get what I want

it strip the R from my id, which I need to keep, but did put the correct value for BLK.

thanks again

[novinosrin]

Hi @ts1993   I'm glad you were able to modify the code to your needs. But I am not sure or rather find that strange something in that simple piece of logic would cause a value to truncate. If you can, please do post what happened how you made the value not to truncate. 

In essence, the code is doing nothing besides a look up/self join  on a condition "exist"