BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
koomalkc
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

14 REPLIES 14
Doc_Duke
Rhodochrosite | Level 12

code missing

koomalkc
Fluorite | Level 6

code is uploading..virus scanner is in work

Reeza
Super User

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.

Tom
Super User Tom
Super User

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?

Ksharp
Super User

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

Tom
Super User Tom
Super User

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

;

koomalkc
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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



koomalkc
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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.

koomalkc
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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;

Reeza
Super User

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;

koomalkc
Fluorite | Level 6

Thanks Reeza.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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