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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

 

View solution in original post

9 REPLIES 9
ballardw
Super User

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";

Ankita
Calcite | Level 5

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

ballardw
Super User

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;

 

Ankita
Calcite | Level 5

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

 

 

Reeza
Super User

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;

 

 

 

 

Ankita
Calcite | Level 5

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


output.png
Reeza
Super User

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. 

Reeza
Super User

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-tex...

Quentin
Super User

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.

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

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
  • 9 replies
  • 1824 views
  • 1 like
  • 4 in conversation