topic Re: Importing multiple csv files from a single directory with a loop in SAS Programming

Importing multiple csv files from a single directory with a loop

Thomasanderson — Thu, 27 Apr 2023 18:38:26 GMT

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!

Re: Importing multiple csv files from a single directory with a loop

PaigeMiller — Thu, 27 Apr 2023 18:43:15 GMT

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.

Re: Importing multiple csv files from a single directory with a loop

Tom — Thu, 27 Apr 2023 18:47:53 GMT

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.

Re: Importing multiple csv files from a single directory with a loop

Kurt_Bremser — Thu, 27 Apr 2023 18:57:00 GMT

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.

Re: Importing multiple csv files from a single directory with a loop

Reeza — Thu, 27 Apr 2023 19:00:31 GMT

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.

Re: Importing multiple csv files from a single directory with a loop

Thomasanderson — Fri, 28 Apr 2023 16:29:42 GMT

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

Re: Importing multiple csv files from a single directory with a loop

Reeza — Fri, 28 Apr 2023 16:41:21 GMT

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-Appendix/ta-p/291716

Re: Importing multiple csv files from a single directory with a loop

Thomasanderson — Fri, 28 Apr 2023 21:06:54 GMT

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