DATA Step, Macro, Functions and more

SAS merge statement equivanet in SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

SAS merge statement equivanet in SQL

Hello,

I have a question on how to use a merge statement in SQL. That I would like to "convert" the following merge statment in SQL.

 

data want;

merge have1 (in=a) have2 (in=b);

by var1;

        if a=1 and b=1 then match='All';

else if a=1 and b=0 then match='have1';

else if a=0 and b=1 then match='have2';

run;

 

I know one can use the where the statement in SQL but that would be equivalent to the match='All' statement above, right? I want to also be able to see where there is data in one table but not the other and vise versa? Is it a join statment? But I also would like to create a new varaible like 'match' so it can flag the rows that match between two datasets and those that dont.

 

Any recommendation would be great!

Thanks


Accepted Solutions
Solution
‎05-31-2017 04:22 PM
Super User
Posts: 19,822

Re: SAS merge statement equivanet in SQL

Posted in reply to sas_student1

Yes, a join is what you need, most likely a full join, I can't seem to paste links for some reason so search sql join and you should see a diagram with red circles. 

 

 

Use a CASE statement with your logic. 

You won't have the automatic variables but you can use the BY variables. 

 

Assuming VAR1 has no missing values, the following is the CASE logic.

 

case when missing(var1) then 'status 1'
       when missing(b.var1) then 'status 2'
     when a.var1=b.var1) then 'match'
else 'CHECKME' end as match

 

 

View solution in original post


All Replies
Solution
‎05-31-2017 04:22 PM
Super User
Posts: 19,822

Re: SAS merge statement equivanet in SQL

Posted in reply to sas_student1

Yes, a join is what you need, most likely a full join, I can't seem to paste links for some reason so search sql join and you should see a diagram with red circles. 

 

 

Use a CASE statement with your logic. 

You won't have the automatic variables but you can use the BY variables. 

 

Assuming VAR1 has no missing values, the following is the CASE logic.

 

case when missing(var1) then 'status 1'
       when missing(b.var1) then 'status 2'
     when a.var1=b.var1) then 'match'
else 'CHECKME' end as match

 

 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 154 views
  • 0 likes
  • 2 in conversation