topic Re: Append Tables in Macro, Please help thanks. in SAS Programming

Append Tables in Macro, Please help thanks.

肉肉 — Fri, 09 Dec 2016 21:31:33 GMT

Hello guys, I was trying to append all the tables together which has the format of

MC201301.MALD_STMT, MC201302.MALD_STMT, MC201303.MALD_STMT......MC201611.MALD_STMT.

I can generate a list of 48 variables from the first set of code, and it gave me MC201301 to MC201612. but I could not Concatenatethese each value to pull the statement. Please help.

Thank you.

data yyyymmtest; /*This is to create a variable as 'MCYYYYMM'*/

MC='MC';

do j = 2013 to 2016;

do i = 01 to 12; /*In here, "i" and "j" are numeric character*/

inew = put(i,z2.); /*convert "i" to be string character*/

Month1=j||strip(inew);

Month = MC||strip(j)||strip(inew); /*The output would be in the form of 'MC201301'*/

output;

end;

run;

proc sql;

select month into:NewMonth from yyyymmtest;

quit;

data test1;

set %do i=1 %to 48;

&NewMonth(i)..MALD_STMT;

%end;

run;

Re: Append Tables in Macro, Please help thanks.

ballardw — Fri, 09 Dec 2016 22:01:39 GMT

Please look at this code:

data temp;
   /* maximum length of libname.dataset is 8+1+32= 41*/
   length dsname $ 41;
   do year=2013 to 2016;
      do month= 1 to 12;
         dsname = cats("MC",year,put(month,z2.),".MALD_STMT");
         output;
      end;
   end;
run;

proc sql noprint;
   select dsname into : Namelist separated by " "
   from temp;
quit;
/*%put &namelist;*/
data combined;
   set
      &namelist
   ;
run;

Your code, while a good begining start has two actual errors. First the %do / %end block is not valid outside of a declared macro. How you used it is is what is referred to as "open code" and should have generated an error message.

Second you would not want the ; immediately after the set name as the first one would end the SET statement.

the generic code for that would look like:

Set

%do i= 1 %to &loopcounter;

datasetname&i

%end;

; <= this ; end the "set" statement.

again that would have to be in a defined macro.

Re: Append Tables in Macro, Please help thanks.

肉肉 — Fri, 09 Dec 2016 22:28:25 GMT

Hello Ballardw, Thank you first of all! great help!

because the month Dec 2016 has not been finished, so I need to remove the last observation which is MC201612.MALD_STMT.

I used the following data line to remove it, but it did not work. can you please tell me why?

if last.dsname then delete;

it did not work at all.

Re: Append Tables in Macro, Please help thanks.

ballardw — Fri, 09 Dec 2016 22:49:43 GMT

The FIRST. and LAST. are only valid when using BY group processing in the data step. And it is set for EACH level of the variable then with what we have done above it would delete everything.

Easiest at this point might be to modify the PROC SQL:

proc sql noprint;
   select dsname into : Namelist separated by " "
   from temp
   where dsname ne "MC201612.MALD_STMT";
quit;

OR

proc sql noprint;
   select dsname into : Namelist separated by " "
   from temp
   where NOT (year=2016 and month=12);
quit;

Re: Append Tables in Macro, Please help thanks.

肉肉 — Fri, 09 Dec 2016 22:54:21 GMT

Thank you again! Its very educational.

Re: Append Tables in Macro, Please help thanks.

肉肉 — Thu, 15 Dec 2016 19:27:18 GMT

Hello, Ballardw,

Before I append all the tables together, I was trying to add one column to display the corresponding year and month, like “201301” for the data from the month of MC201301, how would I modify the code to make it work?

I have tried few ways, and it did not work.

Thank you.