Importing Multiple Files at Once and Keeping File name as Dataset Name in SAS

MellyJ13 — Sun, 21 Mar 2021 17:45:10 GMT

Hi All

I have about 1000 csv files I need to import into SAS from a folder. I found this macro on SAS communities to batch import them, however, it changes the name of the files to dsn1, dsn2, dsn3 etc. and I want to retain the file name as the dataset name in SAS. All the files are stock ticker names (i.e. AAPL.csv MSFT.csv etc.) and I want the imported dataset to be AAPL, MSFT etc. Here is the code:

%macro drive(dir,ext);
%local cnt filrf rc did memcnt name;
%let cnt=0;

%let filrf=mydir;
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));
%if &did ne 0 %then %do;
%let memcnt=%sysfunc(dnum(&did));

%do i=1 %to &memcnt;

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
%if %superq(ext) = %superq(name) %then %do;
%let cnt=%eval(&cnt+1);
%put %qsysfunc(dread(&did,&i));
proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=Dsn&cnt
dbms=csv replace;
run;
%end;
%end;

%end;
%end;
%else %put &dir cannot be opened.;
%let rc=%sysfunc(dclose(&did));

%mend drive;

%drive(C:\path,csv);

Any help would be appreciated!

Re: Importing Multiple Files at Once and Keeping File name as Dataset Name in SAS

Tom — Sun, 21 Mar 2021 19:07:56 GMT

Using macro code to call the data step functions is just going to make the code harder. Your macro is going to generate SAS statements anyway so just use a data step to get the list of file names.

%let path=C:\path
%let ext=csv;

data files ;
  length memname $32 filename $256 ;
  rc = filename('dir',"&dir");
  did = dopen('dir');
  do index=1 to dnum(did);
    filename=catx('\',"&dir",dread(did,index));
    if upcase(scan(filename,-1,'.'))=%upcase("&ext") then do;
      memname=scan(filename,-2,'./');
      output;
    end;
  end;
  rc = dclose('dir');
  rc = filename('dir');
  drop rc did index;
run;

Once you have the list of filenames then it is easy to use it to generate SAS code.

data _null_;
  set files;
  call execute(catx(' '
    ,'proc import datafile=',quote(trim(filename))
    ,'dbms=csv'
    ,'out=',memname,'replace'
    ,';run;'
  ));
run;

But I am not sure using PROC IMPORT to read a series of text files is the right thing to do. PROC IMPORT has to GUESS how to define the fields so each dataset could end up with slightly different structure. It is trivial to read a text file with your own data step. I am also not sure what value you get from putting data into different dataset, just leave the data for all of the stocks in the same dataset. Here is the basic code. Just fill in the details of how to define the variables and read the line from the CSV file into the DO loop.

data want;
  set files ;
  fname=filename;
  infile csv dsd filevar=fname firstobs=2 truncover end=eof;
  do while (not eof);
     input .... ;
     output;
  end;
run;

You could even skip the whole step of searching for the filenames and just let the INFILE statement do that for you.

data want;
  length fname $256 ;
  infile "&dir\*.&ext" dsd filename=fname truncover ;
  input @;
  filename=fname;
  if fname ne lag(fname) then input;
  length stock $10 ;
  stock = scan(filename,-2,'\.');
  input .... ;
run;

topic Re: Importing Multiple Files at Once and Keeping File name as Dataset Name in SAS in New SAS User

Importing Multiple Files at Once and Keeping File name as Dataset Name in SAS

Re: Importing Multiple Files at Once and Keeping File name as Dataset Name in SAS