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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.