topic Re: How to import several .csv files at once into SAS in SAS Programming

How to import several .csv files at once into SAS

AnaV — Tue, 19 Feb 2013 04:37:13 GMT

Hi.

I am new to SAS and I need to import several .csv files simultaneously into sas. I started the procedure using proc import but I have too many files to import then one by one. Here is what I did:

PROC IMPORT OUT= WORK.INSTOWN2000Q1

DATAFILE= "F:\SAS Institutional Ownership\46_q1_2000.csv"

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

PROC IMPORT OUT= WORK.INSTOWN2000Q2

DATAFILE= "F:\SAS Institutional Ownership\45_q2_2000.csv"

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

PROC IMPORT OUT= WORK.INSTOWN2000Q4

DATAFILE= "F:\SAS Institutional Ownership\43_q4_2000.csv"

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

PROC IMPORT OUT= WORK.INSTOWN2000Q3

DATAFILE= "F:\SAS Institutional Ownership\44_q3_2000.csv"

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

data instown2000;

set WORK.INSTOWN2000Q1 WORK.INSTOWN2000Q2 WORK.INSTOWN2000Q3 WORK.INSTOWN2000Q4;

shrout3=shrout1*1000000;

instown=shares/shrout3;

if shrout3=0 then delete;

if stkcdesc= 'COM'or stkcdesc='CMA' or stkcdesc='CMB' or stkcdesc='CMC' or stkcdesc='' ;

run;

I have many more files in my "F:\SAS Institutional Ownership/ folder.

Thank you for your support

Re: How to import several .csv files at once into SAS

MS_Egyptian — Tue, 19 Feb 2013 07:29:03 GMT

Hi AnaV,

As I know Till Now SAS didn't develop a technique to import several CSV files at once. but, you can use macro program to do that i.e.

%Macro ReadCSV (infile , outfile );

PROC IMPORT OUT= &ofile

DATAFILE= &infile

DBMS=CSV REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

%Mend ReadCSV;

%ReadCSV("F:\SAS Institutional Ownership\46_q1_2000.csv", work.ofile1);

%ReadCSV( "F:\SAS Institutional Ownership\45_q2_2000.csv", work.ofile);

and so on for other files

also there is other advanced technique for reading all files from one folder at once - using macros also.

good luck

Re: How to import several .csv files at once into SAS

data_null__ — Tue, 19 Feb 2013 11:57:50 GMT

If the files all have the same fields in the same order you can used the INPUT statement generated with a PROC IMPORT and use a wild card in the FILENAME. You will also want to learn about the INFILE statement options EOV and FILENAME.

data ...;

length filename csvname $256;

retain csvname;

infile "F:\SAS Institutional Ownership\*.csv" eov=eov filename=filename lrecl=?;

if _n_ eq 1 or eov then do;

csvname = filename;

eov=0;

end;

input ....;

other statements possibly

Re: How to import several .csv files at once into SAS

Haikuo — Tue, 19 Feb 2013 15:51:26 GMT

All things considered, if all of your input files whereabout information is already in a table or a flat file or can be put into those files conveniently using OS commands, "Filevar=" in "Infile" statement will be another option.

Haikuo

Re: How to import several .csv files at once into SAS

AxCo — Sun, 17 May 2015 16:13:42 GMT

Since my indexing loop skills are sketchy at best, I like this method. I just build my macro variables with a few keystrokes in a spreadsheet, copy and paste them into the macro you kindly provided, and (BAM!), 40, 80, 200 or so flat files imported in seconds. No errors.

Thanks so much!

Assessment Coordinator

Re: How to import several .csv files at once into SAS

Tom — Sun, 17 May 2015 21:04:33 GMT

You do not want to be using PROC IMPORT for this project. It will need to guess at what the columns represent. You are better off writing a data step to read the CSV files. You could recall the program the PROC IMPORT generated as a starting point if you want.

Re: How to import several .csv files at once into SAS

Reeza — Sun, 17 May 2015 21:19:11 GMT

What indexing/looping? It's a wildcard reference with automatic looping.

Re: How to import several .csv files at once into SAS

AxCo — Mon, 18 May 2015 12:27:05 GMT

By indexing/looping, I referenced other macros I had worked with and/or tried to build, not this macro. In my comment I was trying to be emphatic that the reason why I liked the macro supplied by MS_Egyptian was the lack of such a feature.

Re: How to import several .csv files at once into SAS

AxCo — Mon, 18 May 2015 12:37:47 GMT

I see what you mean. Thanks!