DATA Step, Macro, Functions and more

Re : Merge using Proc SQL

Reply
Contributor
Posts: 68

Re : Merge using Proc SQL

Hi All,

 

I have 7 datasets and I need to extract the common records from all 7 datasets and I wrote a Proc SQL query.

 

Please correct me where i did wrong in the below code.

 

 

proc sql;
create table match as
select *
from success as a full join ringtm as b
on a.mobile_num=b.mobile_num
full join Hung_Up as c
on b.mobile_num=c.mobile_num
full join ub1 as d
on c.mobile_num = d.mobile_num
full join ntwk as e
on d.mobile_num = e.mobile_num
full join ivd as f
on e.mobile_num =f.mobile_num
full join other_error as g
on f.mobile_num =g.mobile_num
where a.mobile_num and b.mobile_num
and c.mobile_num and d.mobile_num and e.mobile_num
and f.mobile_num and g.mobile_num ;
quit;

 

Regards,

Anil

 

Super Contributor
Super Contributor
Posts: 265

Re: Re : Merge using Proc SQL

Posted in reply to anilgvdbm

If you have seven circles ad you only want to select those records where all seven circle overlap, you want an inner join.  

 

 

proc sql;
create table match as
   select * 
   from success a
   inner join ringtm b on a.mobile_num=b.mobile_num
   inner join Hung_Up c on a.mobile_num=c.mobile_num
   inner join ub1 d on on a.mobile_num = d.mobile_num
   inner join ntwk e on a.mobile_num = e.mobile_num
   inner join ivd f on a.mobile_num = f.mobile_num
   inner join other_error g on a.mobile_num = g.mobile_num;
quit;

 

 

PROC Star
Posts: 1,357

Re: Re : Merge using Proc SQL

Posted in reply to anilgvdbm

@anilgvdbm  

 

You mentioned "I need to extract the common records from all 7 datasets"

 

Try using intersect aka set operators

Ask a Question
Discussion stats
  • 2 replies
  • 95 views
  • 2 likes
  • 3 in conversation