topic Re: write a macro to import data from multiple files with sequentially incremental names into one fi in SAS Programming

write a macro to import data from multiple files with sequentially incremental names into one file

Ankita — Fri, 22 Apr 2016 19:02:36 GMT

Hi,

I have 1000 txt files with sequencially increasing naming convention in a shared location.

i.e.

april_1

april_2

april_3

april_4

april_1000

I need to be load all of them using SAS Macro to create one large file.

Thanks and Regards,

Ankita

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

ballardw — Fri, 22 Apr 2016 19:14:45 GMT

Do all of the files have the same structure?

Is there a header row in each file?

Do you have a program that will read one of the files?

If they are the same structure you likly do not need a macro. And if you were planning on using Proc Import were very likely to cause all kinds of issues with character variables being of different lengths in different sets and some variables may be numeric in one and character in another.

If you provide an example TXT file we can likely give you a good start.

Note that you can assign a fileref that identifies multiple files to SAS such as

Filename myfiles "c:\path\April_*.txt";

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

Quentin — Fri, 22 Apr 2016 19:19:23 GMT

You don't need macro for this. You can create a fileref which uses a wild card, e.g.:

filename april "d:\junk\april_*.txt";

data _null_;
  infile april;
  input ;
  put _infile_;
run;

That will read all files that start with april.

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

Ankita — Fri, 22 Apr 2016 19:40:12 GMT

Q1. Do all of the files have the same structure?

A1. Yes all the files have same structure.

Q2. Is there a header row in each file?

A2. Yes there is header in each file

Q3.Do you have a program that will read one of the files?

A3. No I dont have the program.

Thanks,

Ankita

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

ballardw — Fri, 22 Apr 2016 20:06:34 GMT

If the file is delimited in some way such as a comma or space between each variable (colum) then you could run Proc Import on ONE file. The procedure will generate data step code that you can save and modify as needed. If the data is FIXED column then you will have to look up how to read column input (not difficult, just tedious). After you have a program that will read ONE file then you make some modifications.

Change the infile statement to reference either a filereference created with a Filname statement or replace the single file name with the example strings we have shown using a wild card like: Infile "c:\path\April_*.txt"; add the option EOV=skip (This is used to skip over reading the header rows , Firstobs = 2 (to skip reading the header row of the first file)

Before you current input statement add these lines:

input @; /* this looks at the current input line and waits*/

if skip then skip=0; /* The EOV option above sets a temporary variable (SKIP) to a value of 1 when it is the first record of a new file

in the list, reset the value to 0. The Else means that a first line (header row) won't be read*/

Else do;

End;

Run;

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

Reeza — Sat, 23 Apr 2016 00:44:06 GMT

Solve #3.

You can use proc import on one file. Then check your log, it will have the input code you need. You may need to modify it so that it correctly reads in your data. Make sure you have this step working.

Then follow the steps here:

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

Ankita — Sat, 23 Apr 2016 14:49:51 GMT

Thanks for your responses. I have used the option EOV=skip but still find the headers. Is there any advance method to resolve it.

Regards,

Ankita

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

Reeza — Sat, 23 Apr 2016 15:38:33 GMT

There is no EOV=skip option as far as I'm aware.

EOV creates a variable with an indicator if the record is the first in a file. Please read the documentation around this variable.

If you check the code in the link I provided, it shows how to work around headers.

First you set EOV to the variable EOV.

infile "Path\*.txt" eov=eov filename=filename truncover;

Then you don't use the INPUT statement. You'll notice the INPUT is the ELSE for an IF/ELSE condition.

*Check if this is the first record or the first record in a new file;
*If it is, replace the filename with the new file name and move to next line;
if _n_ eq 1 or eov then do;
txt_file_name = scan(filename, -1, "\");
eov=0;
end;

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

Ankita — Sun, 24 Apr 2016 11:04:52 GMT

Thanks for suggesting me the code.

I have written my code accordingly

data Import_all;
Infile "R:\sas\opt\april_2016_*.txt" EOV=skip;
input @;
if skip then skip=0;
Else do;
input Name$ Number Price;
End;
Run;

In the output the header is getting eliminated from 2nd file onwards however there is a blank observation instead.

Also for the first file partially header is getting displayed.

Can you please please help as where exactly I am going wrong.

Also, I have attached the output screen shot below. Obs 1 shows the name which is ideally the variable name.

14 and 27 is blank as they had headers.

Regards,

Ankita

Re: write a macro to import data from multiple files with sequentially incremental names into one fi

Reeza — Sun, 24 Apr 2016 16:06:07 GMT

I'll post more later but here's a quick idea.

Look ok at the link provided above.

Replace ONLY the infile and input statements. The rest of the code will be the same.