CODEWITHSUNDEEP
csvmacrossas
user1106772
user1106772

Reputation: 11

Reading multiple csv files into SAS datasets

I have the following csv files and I need to read them into SAS datasets. Also, I need to assign column names. In addition, I need the column to be numberic but some columns have both number and character values.

folder aa: abc1.csv, abc2.csv, abc3.csv,...... folder bb: abc1.csv, abc2.csv, abc3.csv,...... folder cc: abc1.csv, abc2.csv, abc3.csv,......

Upvotes: 1

Views: 14110

Answers (2)

LonelySoul
LonelySoul

Reputation: 1222

You could do it in following way also.

  1. Keep all your files in one folder.
  2. Name all of them in a csv file with only one column.
  3. Import the file (csv) with file names into SAS.

  4. Create a macro to keep their name with "into" clause.

    proc sql;
select name_list into :name separated by '*' from work.name;
%let count2 = &sqlobs;
quit;

  5. Create a macro like below.

    %macro yy;
%do i = 1 %to &count2;
%let j = %scan(&name,&i,*);
proc import out = &j datafile="folderwhereallcsvfilesarekept\&j..csv"
dbms=csv replace;
getnames = yes;
run; 
%end;
%mend;

Upvotes: 3

itzy
itzy

Reputation: 11755

This isn't a complete answer, but it will get you started. You'll have to add an outer loop to go through the different directories you want to get files from.

/*List the files in a directory for use in a data step. This is written for Windows. Other operating systems will be slightly different. */
filename fnames pipe 'dir c:\temp\* /b';

/* Create a data set with one observation for each file name */
data fnames;
    infile fnames pad missover;
    input @1 filename $255.;
    n=_n_;
run;

/* Store the number of files in a macro variable "num" */
proc sql noprint; select count(filename) into :num; quit;

/* Create a macro to iterate over the filenames, read them in, and append to a data set. */
%macro doit;
    %do i=1 %to #

        proc sql noprint;
            select filename into :filename from fnames where n=&i;
        quit;

        data ds;
            infile &filename;
            input ...list of variable names...;
            ...other statements...;
        run;

        proc append data=ds base=final; run;
    %end;
%mend;

%doit;

Upvotes: 1

Related Questions