Importing multiple csv files from a single directory with a loop

[Thomasanderson]

I have a directory with a large number of csv files. I want to import some of them without writing a separate proc import for each, because that would be silly.

For arguments sake, let's say the files are named filename_ak.csv, filename_al.csv, filename_ct.csv.

A resource I found elsewhere suggested that the following macro would work;

%for(filename, values=filename_ak filename_al filename_ct, do=%nrstr(
PROC IMPORT
DATAFILE= "\\c:\datafiles\&filename.csv"
OUT=WORK.&filename
DBMS=CSV REPLACE;
RUN;
))

But I get error messages:

WARNING: Apparent invocation of macro FOR not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

I am running SAS 9.4 on X64_10PRO

Thanks in advance!

[PaigeMiller]

WARNING: Apparent invocation of macro FOR not resolved.

A macro has to be defined in the program (or included via %include, or included via autocall libraries, or some other way). You haven't done that. SAS doesn't know what macro %for is.

[Thomasanderson]

Thank you. I guess I have a lot to learn about macros.

[Reeza]

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[Tom · Accepted solution]

You cannot call a macro if you haven't defined it to SAS first.

First submit the code that defines %FOR before submitting code that calls it.

But there is no need to use a macro to loop over a list of values. 

That is what a data step does already.

data _null_;
   length filename $256;
   do filename='filename_ak','filename_al','filename_ct';
      call execute(catx(' '
      ,'PROC IMPORT DATAFILE=',quote(cats('\\c:\datafiles\',filename,'.csv'))
      ,'OUT=',cats('WORK.',filename)
      ,'DBMS=CSV REPLACE;'
      ,'RUN;'
     ));
  end;
run;

WARNING: Using PROC IMPORT to read a series of CSV files that should have the exact same variables is a recipe for disaster.  PROC IMPORT will make DIFFERENT GUESSES about variable names, types, lengths and formats based on each file independently.  That will lead to datasets that are incompatible.  

Instead write your own data step to read the CSV file(s) and you will have full control over how the variables are defined.

[Thomasanderson]

Thanks again for this

Can I use something like this to go through the datasets I have created and do some simple operations like "new_var = var1 + var 2;" and "keep new_var var3 var4;" ?

[Kurt_Bremser]

If these files have the same structure, and the data should end up in one dataset, do not use a macro and do not use PROC IMPORT.

Write a data step which either uses a wildcard in the INFILE statement or reads filenames from a dataset and uses the FILEVAR= option.

[Reeza]

If they all have the same layout and you have a data step import code you can read them all at once using the * in the filename as a wildcard character.