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
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
;
code missing
code is uploading..virus scanner is in work
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.
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?
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
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
;
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
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))
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
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.
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
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;
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;
Thanks Reeza.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.