SAS merge statement equivanet in SQL

[sas_student1]

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

[Reeza · Accepted solution]

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