topic Re: Using a DO loop to import/export datasets in SAS Programming

Using a DO loop to import/export datasets

smg3141 — Wed, 11 Aug 2021 20:19:41 GMT

Hello!

I have a large number of .txt files I would like to import, manipulate in the same manner, and export. Is it possible to use a DO loop for this?

For example, I would like to use a DO loop to import file1, file2, file3, etc. instead of typing out an import step for each individual file.

Right now I'm using

%let var1=file1;

proc import datafile="pathname\&var1..txt"
out=example1
dbms = tab replace;
run;

Then changing the macro to file2, file3, etc, and rerunning each time.

Thanks in advance!

Re: Using a DO loop to import/export datasets

jimbarbour — Wed, 11 Aug 2021 20:50:17 GMT

@smg3141 wrote:

Hello!

I have a large number of .txt files I would like to import, manipulate in the same manner, and export. Is it possible to use a DO loop for this?

For example, I would like to use a DO loop to import file1, file2, file3, etc. instead of typing out an import step for each individual file.

Right now I'm using

%let var1=file1;

proc import datafile="pathname\&var1..txt"
out=example1
dbms = tab replace;
run;

Then changing the macro to file2, file3, etc, and rerunning each time.

Thanks in advance!

Yes, you can use the FILEVAR. You could also use a macro do loop (%DO - %END).

See https://support.sas.com/resources/papers/proceedings/proceedings/sugi27/p082-27.pdf for further info on FILEVAR.

Jim

Re: Using a DO loop to import/export datasets

Reeza — Wed, 11 Aug 2021 20:52:20 GMT

Depending on exactly what you're doing you may be able to do it in a single data step using FILEVAR, but otherwise, using macros is a good option. See some examples and tutorials below.

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

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

@smg3141 wrote:

Hello!

I have a large number of .txt files I would like to import, manipulate in the same manner, and export. Is it possible to use a DO loop for this?

For example, I would like to use a DO loop to import file1, file2, file3, etc. instead of typing out an import step for each individual file.

Right now I'm using

%let var1=file1;

proc import datafile="pathname\&var1..txt"
out=example1
dbms = tab replace;
run;

Then changing the macro to file2, file3, etc, and rerunning each time.

Thanks in advance!

Re: Using a DO loop to import/export datasets

smg3141 — Wed, 11 Aug 2021 21:22:39 GMT

Thanks, you paper helped a lot! Here's what I ended up doing. I do have to list out the file names unfortunately, they aren't consistent.

%let var1=example1;
%let var2=example2;
%let var3=example3;

n=1;

%macro importex;
%do n=1 %to 3;
proc import datafile="pathname\&var&n...txt"
out=example&n
dbms = tab replace;
run;
%end;
%mend n;

%importex

Re: Using a DO loop to import/export datasets

Reeza — Wed, 11 Aug 2021 21:50:16 GMT

You may also want to consider this macro then, especially if you can place all the files to be processed in the same folder.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n0ctmldxf23ixtn1kqsoh5bsgmg8.htm

Re: Using a DO loop to import/export datasets

ballardw — Wed, 11 Aug 2021 23:12:59 GMT

If your data sets are supposed to be the same variables and such just coming in different files you may not be happy with the results of Proc Import on multiple files as you may end up with mixed variable types and lengths of character variables.

You might reduce that by adding the Guessingrows=max; to the import code.

If they should all be the same then writing a data step to read them instead of Import may be worth the effort.