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;
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."
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.