Validate left join result

[dht115]

Hello, 

I am requesting some help to validate left join (or any other join) result  to verify we do not have any error. 

I do need to get this in efficient way. Here is an example: 

If I do left join test1 and test2, I need to show that I am not missing any possible observation from test2. 

Thank you, 

data test1;
input var1 $ var2;
datalines;
test1 3
test2 4
test3 5
test4 6
;
run;

data test2;
input var1 $ var3;
datalines;
test1 3
test2 4
test3 5
test5 7
;
run;

/*left join*/
data test3; 
merge test1 (in=a) test2 (in=b);
by var1;
if a;
run;

/*get data from right table only*/
data test4; 
merge test3 (in=a) test2 (in=b);
by var1;
if b and not a;
run;

/*validate left join data*/
/*We will get 0 observation*/
data test5;
merge test3 (in=a) test4 (in=b);
by var1;
if b and a;
run;

[ballardw]

MERGE should not be considered as an SQL style join. There are lots of reasons but the main one is what happens with like named variables not on the By statement. Second is what happens when there are duplicates of By variables in both sets. So if you hope to use data step merge to "validate" and SQL style left, right or other join then you need to provide a great deal more about the different sets and the expected result of the join.

I am not sure what you are actually expecting to see to answer your question. I really can't decipher this: "If I do left join test1 and test2, I need to show that I am not missing any possible observation from test2."