topic Re: Trying to load multiple datasets into SAS in SAS Programming

Trying to load multiple datasets into SAS

mattkancy — Sun, 16 Oct 2016 22:26:20 GMT

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

Re: Trying to load multiple datasets into SAS

Reeza — Sun, 16 Oct 2016 23:04:24 GMT

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-text-files-that-have/ta-p/223627

Re: Trying to load multiple datasets into SAS

Patrick — Sun, 16 Oct 2016 23:08:04 GMT

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#n1rill4udj0tfun1fvce3j401plo.htm

Re: Trying to load multiple datasets into SAS

Kurt_Bremser — Mon, 17 Oct 2016 06:59:24 GMT

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.