topic Re: Macro for joining tables in SAS Programming

Macro for joining tables

koomalkc — Fri, 13 Feb 2015 14:44:15 GMT

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;

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

Re: %macro test1;

Doc_Duke — Fri, 13 Feb 2015 14:46:09 GMT

code missing

Re: %macro test1;

koomalkc — Fri, 13 Feb 2015 15:32:24 GMT

code is uploading..virus scanner is in work

Re: Macro for joining tables

Reeza — Fri, 13 Feb 2015 16:14:31 GMT

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.

Re: Macro for joining tables

Tom — Fri, 13 Feb 2015 16:26:25 GMT

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?

Re: Macro for joining tables

Ksharp — Sat, 14 Feb 2015 15:16:16 GMT

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;

quit;

%mend test1;

%test1

Re: Macro for joining tables

Tom — Sat, 14 Feb 2015 18:17:41 GMT

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;

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

;

Re: Macro for joining tables

koomalkc — Sat, 14 Feb 2015 22:41:39 GMT

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

Re: Macro for joining tables

Tom — Sat, 14 Feb 2015 23:33:21 GMT

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))

Re: Macro for joining tables

koomalkc — Mon, 16 Feb 2015 23:04:19 GMT

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;

run;

%mend trgt;

%trgt

Thanks

Re: Macro for joining tables

Tom — Tue, 17 Feb 2015 00:50:13 GMT

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.

Re: Macro for joining tables

koomalkc — Tue, 17 Feb 2015 04:25:14 GMT

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

Re: Macro for joining tables

Reeza — Tue, 17 Feb 2015 05:18:01 GMT

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;

Re: Macro for joining tables

Tom — Tue, 17 Feb 2015 12:40:38 GMT

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;

;

run;

%mend trgt;

Re: Macro for joining tables

koomalkc — Wed, 18 Feb 2015 17:05:55 GMT

Thanks Reeza.