topic Re: Translate data merge into Proc SQL join, create new variable based on presence/absence in one of the datasets in SAS Procedures

Translate data merge into Proc SQL join, create new variable based on presence/absence in one of the datasets

metro — Tue, 25 Nov 2014 14:51:09 GMT

I often merge data and use the in= processing to create new variables (example below). It looks like this could be done with the case statement in SQL, but I can't get it to work.

data test;

merge one (in=inone)

two (in=intwo);

by ID;

if inone and not intwo then Notfound=1;

else Notfound=0;

run;

Re: Translate data merge into Proc SQL join, create new variable based on presence/absence in one of the datasets

RW9 — Tue, 25 Nov 2014 14:54:35 GMT

Hi,

proc sql;

create table TEST as

select COALESCE(A.ID,B.ID) as ID,

case when A.ID is null or B.ID is null then 1

else 0 end as NOTFOUND

from ONE A

full join TWO B

on A.ID=B.ID;

quit;

The full join will create a list of all rows from one or the other, with the first missing if not in second and viceversa. So when either is null will highlight where missing.

Re: Translate data merge into Proc SQL join, create new variable based on presence/absence in one of the datasets

Kurt_Bremser — Tue, 25 Nov 2014 14:57:08 GMT

You can check on b.id.

proc sql;

create table want as

select a.*,

case

when b.id is missing

then 0

else 1

end as notfound

from one a left join two b

;

quit;