topic Re: batch process data files with contionout file names in SAS Programming

batch process data files with contionout file names

fengyuwuzu — Wed, 20 Jan 2016 19:59:38 GMT

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

Re: batch process data files with contionout file names

Reeza — Wed, 20 Jan 2016 20:03:39 GMT

Yes, look into Call Execute.

Re: batch process data files with contionout file names

Astounding — Wed, 20 Jan 2016 20:19:22 GMT

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.

Re: batch process data files with contionout file names

fengyuwuzu — Wed, 20 Jan 2016 20:37:37 GMT

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

Re: batch process data files with contionout file names

Astounding — Wed, 20 Jan 2016 20:44:58 GMT

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?

Re: batch process data files with contionout file names

fengyuwuzu — Wed, 20 Jan 2016 20:50:42 GMT

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

Re: batch process data files with contionout file names

Astounding — Wed, 20 Jan 2016 21:02:36 GMT

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.

Re: batch process data files with contionout file names

fengyuwuzu — Wed, 20 Jan 2016 21:21:39 GMT

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;

Re: batch process data files with contionout file names

Astounding — Wed, 20 Jan 2016 21:21:53 GMT

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?

Re: batch process data files with contionout file names

fengyuwuzu — Wed, 20 Jan 2016 21:24:10 GMT

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.

Re: batch process data files with contionout file names

Astounding — Wed, 20 Jan 2016 21:32:18 GMT

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.

Re: batch process data files with contionout file names

fengyuwuzu — Wed, 20 Jan 2016 21:40:43 GMT

%sysfunc(putN(&n,z2) is the thing I was looking for.

Thank you very much!