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

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;

end; 

run;

 

run;

proc sql;

select month into:NewMonth from yyyymmtest;

quit;

 

 

data test1;

set %do i=1 %to 48;

&NewMonth(i)..MALD_STMT;

%end;

run;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

5 REPLIES 5
ballardw
Super User

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.

肉肉
Calcite | Level 5

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.

 

 

ballardw
Super User

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;
肉肉
Calcite | Level 5

Thank you again! Its very educational.

肉肉
Calcite | Level 5

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.

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
  • 5 replies
  • 1788 views
  • 0 likes
  • 2 in conversation