SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

 

Hi,

 

I've 2 datasets which has 6 columns in 1 dataset & another has 8 columns. But first 5 columns are same in both. Say first dataset name is A and another B. I want to merge the 2 dataset such that I get 9 total columns. Generally we merge using SQL in the following way:

 

 

proc sql;
create table example as
select a.*,b.var1,b.var2,b.var3
from A as a left join B as b
on a.key1 = b.key1 and a.key2 = b.key2 and a.key3 = b.key3 and a.key4 = b.key4 and a.key5 = b.key5;
quit;

And if I need to merge the table again and again, I've to repeatedly write the 5 keys again and again.

 

My question is, is there any way I can pass the key in macro and call the macro while merging? 

 

Thanks!


Accepted Solutions
Solution
‎11-09-2017 05:09 AM
Super User
Posts: 20,203

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

Posted in reply to newbie_ari

All Replies
Super User
Posts: 20,203

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

Posted in reply to newbie_ari

Try a Natural JOIN instead.

Contributor
Posts: 22

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

@Reeza: Thanks for your reply. But could you please elaborate on what you mean by natural join.
Solution
‎11-09-2017 05:09 AM
Super User
Posts: 20,203

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

Posted in reply to newbie_ari
Contributor
Posts: 22

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

@Reeza: Thanks a lot! Your answer was so uncommon I didn't bother to google it. I thought you wrote something wrong. I'm working on SQL for quite sometime & had the impression that I know SQL pretty well. But life has it's own way to show you your limitations & keeps you humble. Thanks again!
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 87 views
  • 1 like
  • 2 in conversation