DATA Step, Macro, Functions and more

Trying to load multiple datasets into SAS

Reply
Occasional Learner
Posts: 1

Trying to load multiple datasets into SAS

Hello Everyone,

 

I have the following SAS code running, and I'm trying to get multiple CSV files to run in SAS. Is that somehting that is possible. Below is my current SAS Code:

 

FILENAME CSV "/home/kancym0/sasuser.v94/DEF.csv" TERMSTR=LF;

PROC IMPORT DATAFILE=CSV
            OUT=WORK.MYCSV
            DBMS=CSV
            REPLACE;
RUN;

FILENAME CSV "/home/kancym0/sasuser.v94/K.csv" TERMSTR=LF;

PROC IMPORT DATAFILE=CSV
            OUT=WORK.MYCSV
            DBMS=CSV
            REPLACE;
RUN;


FILENAME CSV "/home/kancym0/sasuser.v94/QB.csv" TERMSTR=LF;

PROC IMPORT DATAFILE=CSV
            OUT=WORK.MYCSV
            DBMS=CSV
            REPLACE;
RUN;

FILENAME CSV "/home/kancym0/sasuser.v94/RB.csv" TERMSTR=LF;

PROC IMPORT DATAFILE=CSV
            OUT=WORK.MYCSV
            DBMS=CSV
            REPLACE;
RUN;

FILENAME CSV "/home/kancym0/sasuser.v94/ST.csv" TERMSTR=LF;

PROC IMPORT DATAFILE=CSV
            OUT=WORK.MYCSV
            DBMS=CSV
            REPLACE;
RUN;

FILENAME CSV "/home/kancym0/sasuser.v94/TE.csv" TERMSTR=LF;

PROC IMPORT DATAFILE=CSV
            OUT=WORK.MYCSV
            DBMS=CSV
            REPLACE;
RUN;

FILENAME CSV "/home/kancym0/sasuser.v94/WR.csv" TERMSTR=LF;

PROC IMPORT DATAFILE=CSV
            OUT=WORK.MYCSV
            DBMS=CSV
            REPLACE;
RUN;

 

I have individual codes for each dataset. Is there a way I can load al of the datasets into SAS at once whether it's by what I'm doing above, or is there another code I can use to get them all in at once? Thank you very much in advance for the help

Super User
Posts: 19,855

Re: Trying to load multiple datasets into SAS

Posted in reply to mattkancy

All your OUT datasets have the same name so only the last one will exist.

 

You can use several methods but all require a data step rather than proc import.

This assumes that all your datasets have the same structure, if they don't then you'll need a method similar to what you're currently doing.

 

You can modify the code in the link below for your situation by adding the TERMSTR option to the INFILE statement. 

 

 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

Respected Advisor
Posts: 4,173

Re: Trying to load multiple datasets into SAS

[ Edited ]
Posted in reply to mattkancy

Assuming the structure of all your .csv is the same then you could use a SAS Data step and a wildcard in your filename statement. This would read all of your .csv files in this location.

 

If you can't use a wildcard as there would also be other .csv being picked up then have a look into "Example 5: Reading from Multiple Input Files".

http://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n1rill4udj0tfun...

 

Super User
Posts: 7,844

Re: Trying to load multiple datasets into SAS

Posted in reply to mattkancy

This is a situation where wrapping code into a macro can be handy.

 

First, let's wrap your import code into a macro and deal with the dataset name issue on the way:

%macro import_csv(filename);
filename csv "/home/kancym0/sasuser.v94/&filename..csv" termstr=LF;

proc import
  datafile=csv
  out=work.mycsv_&filename.
  dbms=CSV
  replace
;
run;
%mend;

Next, create a list of filenames to automate processing:

data files;
input filename $;
datalines;
DEF
K
QB
RB
ST
TE
WR
;
run;

Now, use that list to call the macro repeatedly:

data _null_;
set files;
call execute('%import_csv('!!trim(filename)!!');');
run;

If you don't need the list of filenames in a dataset for further processing, you can combine the last 2 steps into one data _null_ step.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 320 views
  • 0 likes
  • 4 in conversation