DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

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

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. 

 


Accepted Solutions
Solution
‎03-01-2017 12:26 AM
Super User
Super User
Posts: 7,942

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

Posted in reply to vsharipriya
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.

View solution in original post


All Replies
Solution
‎03-01-2017 12:26 AM
Super User
Super User
Posts: 7,942

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

Posted in reply to vsharipriya
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.

Contributor
Posts: 30

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

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

Contributor
Posts: 54

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

Posted in reply to vsharipriya
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;
Contributor
Posts: 30

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

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
Contributor
Posts: 54

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

Posted in reply to vsharipriya
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.);
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 180 views
  • 1 like
  • 3 in conversation