topic Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES in SAS Programming

SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

newbie_ari — Wed, 08 Nov 2017 17:49:49 GMT

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!

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

Reeza — Wed, 08 Nov 2017 18:08:29 GMT

Try a Natural JOIN instead.

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

newbie_ari — Thu, 09 Nov 2017 03:41:01 GMT

@Reeza: Thanks for your reply. But could you please elaborate on what you mean by natural join.

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

Reeza — Thu, 09 Nov 2017 03:43:40 GMT

Google is your friend.

http://www.datasavantconsulting.com/roland/natjoin.html

Re: SQL MERGE USING MACRO FOR MULTIPLE VARIABLES

newbie_ari — Thu, 09 Nov 2017 10:08:58 GMT

@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!