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

[metro]

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;

[RW9]

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.

[Kurt_Bremser · Accepted solution]

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;