We’re smarter together. Learn from this collection of community knowledge and add your expertise.

How do I write a macro to...import multiple text files that have the same format?

by Super User ‎02-13-2015 08:36 PM - edited ‎01-25-2016 12:13 AM (2,710 Views)

This is part two of an ongoing series into how to accomplish a task WITHOUT using a macro.

In the previous post, I split a file without using a macro. In this post, I go over how

read in text files that have the same format and create a single file.

 

One method of doing so is:

  1. Use the OS to generate a list of file names
  2. Write the code to read a single file
  3. Convert that to a macro
  4. Read each file in using the macro and append the results

 

Instead of this method, SAS provides a wildcard method of referring to files.

 

This means if my file reference looks like:

 

 

"/folders/myfolders/*.csv"

 

 

SAS will read all CSV folders in the folder myfolders.

 

You can limit your wild card as well using a more specific naming convention

For example, the follow line of code will import all files that start with the word cars:

 

 

"/folders/myfolders/cars*.csv"

 

 

 

However there are some things to consider:

  • Headers in each file
  • How do I know which record a file comes from

 

Fortunately SAS also provides options for those, EOV allows us to identify the first

record in a file and FILENAME allows us to identify the file name of the file imported.

 

Enough talking, here's what the full code would look like. Hope you find it useful!

 

 

 

data import_all;
 
*make sure variables to store file name are long enough;
length filename txt_file_name $256;
 
*keep file name from record to record;
retain txt_file_name;
 
*Use wildcard in input;
infile "Path\*.txt" eov=eov filename=filename truncover;
 
*Input first record and hold line;
input@;
 
*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;
 
*Otherwise  go to the import step and read the files;
else input
 
*Place input code here;
 
;
run;

 

 

This code is also available on GitHub

Comments
by Respected Advisor
on ‎02-13-2015 10:23 PM

Nice material . Macro programming is often used when it is not really needed. Code writing code is harder to read and to maintain.

In your example,why txt_file_name = scan(filename, -2, ".\") ? File names can contain zero, one or more than one period characters. Wouldn't txt_file_name = scan(filename, -1, "\") be simpler and still do the job?

PG

by Super User
on ‎02-13-2015 10:48 PM

Thanks Pierre.  You are correct and I'll modify the code to reflect this.

In general, I avoid periods in my file names but I guess others don't follow that rule :smileyhappy:. 

by Super Contributor
on ‎01-28-2017 09:28 PM

Hi,

 

could you please specify what you mean by 

*Place input code here;

 What I did was simply input the names of the variables but in the data step they were empty 

ps: I want to import xls files.

 

Thank you

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.