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
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;
<here you put it your working program's INPUT statement and any other code you may have>
End;
Run;
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";
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
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;
<here you put it your working program's INPUT statement and any other code you may have>
End;
Run;
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
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;
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
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.
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:
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.