DATA Step, Macro, Functions and more

Macro for joining tables

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Macro for joining tables

Hi,

I'm working to write a macro that is intended to join 8 tables one to one and get output as a 8 different joined tables, and want to do it at once using a macro. Below is my code, but it did n't workout. Please somebody help me to correct and understand it.

I've 8 data_1&i.&k tables (like data_20123 - data_201314-one table for each quater for each month), also I've 8 target1&i.q&j (like target12q1-target13q4  ----8 tables). And key to join table is id.

Below are the code.

%macro test1;

%do i= 2 %to 3 %by 1;

%do j= 1 %to 4 %by 1;

%do k= 3 %to 12 %by 3;

proc sql;

create outdata_201&i.q&j as

select T1*,T2.target

from data_201&i.&k as T1 left join target1&i.q&j as T2

on T1.id=T2.id;

%end;

%end;

%end;

quit;

%mend test1;

%test1

/***********************************/

Please see the attached file for the dataset codes.

/******************************/

Though code worked, but it gave me misleading output. Each output table has same id and same observations. For example all the 4 output  tables for year 2012 has the the same obs as 2012 quater 4 tables has, and same for 2013 output dataset.

Thanks,

_kamal

Message was edited by: Kamal KC

Attachment

Accepted Solutions
Solution
‎02-14-2015 01:17 PM
Super User
Super User
Posts: 7,042

Re: Macro for joining tables

Since you said you wanted 8 output tables perhaps you want to union the four queries generated by the inner most loop?

%macro test1;

proc sql;

%do i= 2 %to 3 %by 1;

    %do j= 1 %to 4 %by 1;

        create table outdata_201&i.q&j as

        %do k= 3 %to 12 %by 3;

           %if &k ne 3 %then union ;

            select T1.*,T2.target

            from data_201&i.&k as T1 left join target1&i.q&j as T2

            on T1.id=T2.id

        %end;

        ;

    %end;

%end;

quit;

%mend test1;

options mprint;

%test1 ;

Generates queries like this:

create table outdata_2012q1 as

select T1.*,T2.target

from data_20123 as T1 left join target12q1 as T2

on T1.id=T2.id

union

select T1.*,T2.target

from data_20126 as T1 left join target12q1 as T2

on T1.id=T2.id

union

select T1.*,T2.target

from data_20129 as T1 left join target12q1 as T2

on T1.id=T2.id

union

select T1.*,T2.target

from data_201212 as T1 left join target12q1 as T2

on T1.id=T2.id

;

View solution in original post


All Replies
Trusted Advisor
Posts: 2,115

Re: %macro test1;

code missing

Contributor
Posts: 30

Re: %macro test1;

code is uploading..virus scanner is in work

Super User
Posts: 19,789

Re: Macro for joining tables

Did you run it with MPRINT and SYMBOLGEN on to see what the macro variables are resolving to?

Can you post the log from such a run - just one iteration is fine.

Super User
Super User
Posts: 7,042

Re: Macro for joining tables

Since the output table name only depends on I and J you are overwriting the table for subsequent values of K.

create outdata_2012q1 as

select T1*,T2.target

from data_20123 as T1

left join target12q1 as T2

on T1.id=T2.id ;

create outdata_2012q1 as

select T1*,T2.target

from data_20126 as T1

left join target12q1 as T2

on T1.id=T2.id ;

...


What is it that you actually want to do?

Super User
Posts: 10,023

Re: Macro for joining tables

Your code looks good.

%macro test1;

proc sql;

%do i= 2 %to 3 %by 1;

%do j= 1 %to 4 %by 1;

%do k= 3 %to 12 %by 3;

create outdata_201&i.q&j as

select T1*,T2.target

from data_201&i.&k as T1 left join target1&i.q&j as T2

on T1.id=T2.id;

%end;

%end;

%end;

quit;

%mend test1;

%test1

Solution
‎02-14-2015 01:17 PM
Super User
Super User
Posts: 7,042

Re: Macro for joining tables

Since you said you wanted 8 output tables perhaps you want to union the four queries generated by the inner most loop?

%macro test1;

proc sql;

%do i= 2 %to 3 %by 1;

    %do j= 1 %to 4 %by 1;

        create table outdata_201&i.q&j as

        %do k= 3 %to 12 %by 3;

           %if &k ne 3 %then union ;

            select T1.*,T2.target

            from data_201&i.&k as T1 left join target1&i.q&j as T2

            on T1.id=T2.id

        %end;

        ;

    %end;

%end;

quit;

%mend test1;

options mprint;

%test1 ;

Generates queries like this:

create table outdata_2012q1 as

select T1.*,T2.target

from data_20123 as T1 left join target12q1 as T2

on T1.id=T2.id

union

select T1.*,T2.target

from data_20126 as T1 left join target12q1 as T2

on T1.id=T2.id

union

select T1.*,T2.target

from data_20129 as T1 left join target12q1 as T2

on T1.id=T2.id

union

select T1.*,T2.target

from data_201212 as T1 left join target12q1 as T2

on T1.id=T2.id

;

Contributor
Posts: 30

Re: Macro for joining tables

Hi Tom,

THANK YOU VERY MUCH !!!

You find where the bug is. Yes, you are wright, looping of k value should be in innner loop.

I have one more question for you. Some of my tables are like data_201203, data_201206,data_201209,data_201212 for year 2012 and data_201303,data_201306,data_201309, and data_201312 for year 2013. Here datasets have represented quater by 03, 06, 09 and 12 instead of 3,6,, and 9. So, how the counter of k should be written. I tried to run with %do k= 03 %to 12 %by 3; and it gave  errors while reading table for 4th quater (i.e data_201212 and data_201312). Because code will read a table like data_2012012, and it did not find the table.

I have just uploaded my sas code (please see the attachment)  which I ran a little while before, and I ended up with errors.

Thanks in advance,

_kamal

Attachment
Super User
Super User
Posts: 7,042

Re: Macro for joining tables

Use the %SYFUNC() funciton to call the PUTN() function so that you can use the Z format to put in the leading zeros..

data_201&i.%sysfunc(putn(&k,Z2))



Contributor
Posts: 30

Re: Macro for joining tables

Tom,

I have next question. Here I'm trying to appand 8 tables to one table name target. When I ran the code  I found each table is replaced by another table (instead of appending)and I got output which contains data from last table only  i.e data_201312. Having a problem why the output in not accumulating or tables are not appending.

Below is the code I ran.

%macro trgt;

data target;

%do i= 2 %to 3;

%do k= 01 %to 04;

set data_201&i.%sysfunc(putn(&k,z2));

%end;

%end;

run;

%mend trgt;

%trgt

Thanks

Super User
Super User
Posts: 7,042

Re: Macro for joining tables

If you turn on the option MPRINT you can see the code that you macro is generating.

Then the mistake is obvious.

Instead of generating one SET statement you have generated 8 separate SET statements.  So only the records from the last will be read.

Just move the SET before the %DO loops and the semi-colon after the %DO loops.

Contributor
Posts: 30

Re: Macro for joining tables

Tried to run the code as you mentioned, still getting errors. I moved SET before %DO loops and did not understand what you mean by ...and the semi-colon after %DO loops.

Could you please elaborate the solution with codes.

Thanks

Super User
Super User
Posts: 7,042

Re: Macro for joining tables

I have put the macro statements in red so it is easier to see the code that the macro is generating.

Notice that the keyword SET is before the %DO loops and the ending semi-colon for the SET is after the %DO loops.


%macro trgt;

data target;

  SET

%do i= 2 %to 3;

%do k= 01 %to 04;

  data_201&i.%sysfunc(putn(&k,z2))

%end;

%end;

  ;

run;

%mend trgt;

Super User
Posts: 19,789

Re: Macro for joining tables

Your macro will generate:

data target;

set data_201201;

set data_201202;

set data_201203;

...

set data_201304;

run;

What you actually want to generate is:

data target;

set

data_201201

data_201202

...

data_201304;

run;

Note the single SET and where the semi colon ends up.

An alternative solution is to not use macros and use something as follows:

data target;

set data_201201-data_201204 data201301-data201304;

run;

Contributor
Posts: 30

Re: Macro for joining tables

Thanks Reeza.

🔒 This topic is solved and locked.

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

Discussion stats
  • 14 replies
  • 2230 views
  • 6 likes
  • 5 in conversation