topic Re: Syntax/code to replace an observation with its related observation in SAS Programming

Syntax/code to replace an observation with its related observation

ts1993 — Wed, 21 Aug 2019 17:22:55 GMT

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.

Re: Syntax/code to replace an observation with its related observation

Reeza — Wed, 21 Aug 2019 17:25:42 GMT

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?

Re: Syntax/code to replace an observation with its related observation

ts1993 — Wed, 21 Aug 2019 17:29:24 GMT

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

Re: Syntax/code to replace an observation with its related observation

novinosrin — Wed, 21 Aug 2019 17:43:10 GMT


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;

Re: Syntax/code to replace an observation with its related observation

Reeza — Wed, 21 Aug 2019 17:40:18 GMT

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.

Re: Syntax/code to replace an observation with its related observation

Reeza — Wed, 21 Aug 2019 17:41:37 GMT

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

BD11234
BD11234R2

Re: Syntax/code to replace an observation with its related observation

novinosrin — Wed, 21 Aug 2019 17:47:23 GMT

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

Re: Syntax/code to replace an observation with its related observation

Reeza — Wed, 21 Aug 2019 18:06:33 GMT

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.

Re: Syntax/code to replace an observation with its related observation

ts1993 — Wed, 21 Aug 2019 20:48:26 GMT

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

Re: Syntax/code to replace an observation with its related observation

novinosrin — Wed, 21 Aug 2019 20:54:09 GMT

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"