How to import several .csv files at once into SAS

Reply
Contributor
Posts: 20

How to import several .csv files at once into SAS

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

Occasional Contributor
Posts: 14

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

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

New Contributor
Posts: 3

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

Posted in reply to MS_Egyptian

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

Super User
Posts: 19,870

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

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

New Contributor
Posts: 3

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

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.

Respected Advisor
Posts: 3,799

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

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

Respected Advisor
Posts: 3,156

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

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

Super User
Super User
Posts: 7,076

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

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.

New Contributor
Posts: 3

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

I see what you mean. Thanks!

Ask a Question
Discussion stats
  • 8 replies
  • 16230 views
  • 4 likes
  • 7 in conversation