I have two datasets:
data one;
input id acctype$ catalogtype$ amount;
datalines;
1 credit issued 10
1 credit refund 5
1 credit cancelled 20
2 debit issued 20
2 debit refund 20
3 cash issued 20
run;
data two;
input id acctype$ catalogtype$ amount;
datalines;
1 credit issued 10
1 credit refund 10
1 credit cancelled 10
2 debit issued 20
2 debit refund 10
4 cash refund 10
run;
I want a output that should get matched records from left table, and non matching records from right table like this:
1 credit issued 10 matched
1 credit refund 5 not-matched
1 credit cancelled not-matched
2 debit issued 20 matched
2 debit refund 20 not-matched
3 cash issued 20 not-matched
I want this specifically using proc sql, please. I tried left outer join and union all of both datasets but not able to get solution.
proc sql; create table WANT as select A.*, case when B.ID is not null then "Matched" else "UnMatched" end as RES from ONE A left join TWO B on A.ID=B.ID and A.ACCTYPE=B.ACCTYPE and A.CATALOGTYPE=B.CATALOGTYPE and A.AMOUNT=B.AMOUNT; quit;
Not tested, but that should work.
proc sql; create table WANT as select A.*, case when B.ID is not null then "Matched" else "UnMatched" end as RES from ONE A left join TWO B on A.ID=B.ID and A.ACCTYPE=B.ACCTYPE and A.CATALOGTYPE=B.CATALOGTYPE and A.AMOUNT=B.AMOUNT; quit;
Not tested, but that should work.
Yes. This worked. I was joining using only id and hence wasnt getting the solution as I wanted.
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.