Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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;


Accepted Solutions
Solution
‎11-25-2014 09:57 AM
Super User
Posts: 7,782

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

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

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

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.

Solution
‎11-25-2014 09:57 AM
Super User
Posts: 7,782

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

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 227 views
  • 3 likes
  • 3 in conversation