DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

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


Accepted Solutions
Solution
‎05-06-2016 03:17 PM
Super User
Posts: 11,343

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

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


All Replies
Super User
Posts: 11,343

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

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

Occasional Contributor
Posts: 8

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

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

Solution
‎05-06-2016 03:17 PM
Super User
Posts: 11,343

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

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;

 

Occasional Contributor
Posts: 8

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

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

 

 

Super User
Posts: 19,855

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

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;

 

 

 

 

Occasional Contributor
Posts: 8

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

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
Super User
Posts: 19,855

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

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. 

Super User
Posts: 19,855

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

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

PROC Star
Posts: 1,324

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

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 446 views
  • 1 like
  • 4 in conversation