topic Re: How do i use proc sql to get matched/unmatched records from two datasets in SAS Programming

How do i use proc sql to get matched/unmatched records from two datasets

vsharipriya — Mon, 27 Feb 2017 09:40:38 GMT

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.

Re: How do i use proc sql to get matched/unmatched records from two datasets

RW9 — Mon, 27 Feb 2017 09:43:36 GMT

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.

Re: How do i use proc sql to get matched/unmatched records from two datasets

vsharipriya — Wed, 01 Mar 2017 05:25:51 GMT

Yes. This worked. I was joining using only id and hence wasnt getting the solution as I wanted.

Re: How do i use proc sql to get matched/unmatched records from two datasets

Yavuz — Wed, 01 Mar 2017 05:33:51 GMT

Try this please

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;
quit;

Re: How do i use proc sql to get matched/unmatched records from two datasets

vsharipriya — Wed, 01 Mar 2017 05:41:00 GMT

Thanks for the solution. But as i said, joining with just the id did not do my job.Had to join on all the fields like previous solution. Because I want to look at all the fields to match on amount

Re: How do i use proc sql to get matched/unmatched records from two datasets

Yavuz — Wed, 01 Mar 2017 18:31:47 GMT

Sorry. I suggest that create a new variable as newvar and join them:

Data one1;
Set one;
Newvar=put(id,10.)||acctype||catalogtype||put(amount,2.);

Data two1;
Set two;
Newvar=put(id,10.)||acctype||catalogtype||put(amount,2.);