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

I have 12 files, file name like "xxxx201501.csv", "xxxx201502.csv", ... "xxxx201512.csv"

 

if I want to do the same opration on them, can I use a loop and macro varaible to do it?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

OK, given that you can get the program to work for 1 file, here is a way to write the loop.

 

%macro run_me;

 

%local n n2;

%do n=1 %to 12;

   %let n2 = %sysfunc(putn(&n, z2));

   data output&n2;

   infile "xxx2015&n2";

   ...

   run;

%end;

 

%mend run_me;

 

Clearly, it would be possible to add other parameters such as names that replace "output" and "xxx".

 

It's your choice whether you want to name the outputs with or without leading zeros:  output&n2 vs. output&n

 

Good luck.

View solution in original post

11 REPLIES 11
Reeza
Super User

Yes, look into Call Execute.

Astounding
PROC Star

What do you already know how to do with these 12 names?

 

  • Issue a system command to display the names?
  • Get the names of the files into a text file?
  • Get the names of the files into a SAS data set?
  • Nothing?  (In that case, we might need to know the operating system.)

Yes, you certainly can create a macro and loop through the names.  But depending on what you want to do with them, a macro might not even be necessary.

fengyuwuzu
Pyrite | Level 9
for example, I want to read in these 12 files and output 12 new files:
data work.datast01;
infile "xxxx201501.csv" ...;




Astounding
PROC Star

If macro language could get you the right INFILE statements, would you know how to write the INPUT statement?

 

Is your ultimate goal 12 separate SAS data sets, or 1 large SAS data set combining all the data from the 12 files?

fengyuwuzu
Pyrite | Level 9
I want to have 12 output data sets

I was thinking about a do loop, but I do not know how to replace the 01, 02, ... 12 with the count variable
Astounding
PROC Star

OK.  Here are two specific tasks you will need to complete in order to take this further.

 

1. What operating system command can take the names of all 12 files and put the names into a text file?  What does the text file look like?

 

2. What program would it take to read in a single one of the files, and create a SAS data set from that single file?  No macro language is involved.  Hard-code everything needed.

 

 

fengyuwuzu
Pyrite | Level 9

The code below works. But only for files 01-09.

Is there a way to include 10, 11 and 12 in the same macro?

 

%macro run_me;
%let n=1;

%do %while (&n <= 9);
%let filein = xxxx20150&n..csv;

data output&n;
infile &filein;
<data step-->
run;

%let n=%eval(&n+1);
%end;
%mend;

 

Astounding
PROC Star

A loop like that is possible.

 

Do you know what the rest of the DATA step would look like?

 

Do all the .csv files have the same structure?

fengyuwuzu
Pyrite | Level 9

yes, they have the same structure. It is for the same data set in different months.

 

I inserted my code which was used to read a single file, and it worked. Now I need to think about how to include 10, 11 and 12 three files.

Astounding
PROC Star

OK, given that you can get the program to work for 1 file, here is a way to write the loop.

 

%macro run_me;

 

%local n n2;

%do n=1 %to 12;

   %let n2 = %sysfunc(putn(&n, z2));

   data output&n2;

   infile "xxx2015&n2";

   ...

   run;

%end;

 

%mend run_me;

 

Clearly, it would be possible to add other parameters such as names that replace "output" and "xxx".

 

It's your choice whether you want to name the outputs with or without leading zeros:  output&n2 vs. output&n

 

Good luck.

fengyuwuzu
Pyrite | Level 9
%sysfunc(putN(&n,z2) is the thing I was looking for.

Thank you very much!

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
  • 11 replies
  • 1167 views
  • 1 like
  • 3 in conversation