topic Importing multiple csv files in SAS Programming

Importing multiple csv files

bharath86 — Wed, 18 Jan 2023 17:03:40 GMT

Hi,

This is an example macro from sas documentation.

%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;
                  guessingrows=max;
               run;
            %end;
         %end;
       %end;
    %end;
  %else %put &dir cannot be opened.;

  %let rc=%sysfunc(dclose(&did));
%mend drive;

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n0ctmldxf23ixtn1kqsoh5bsgmg8.htm

How do I modify it, so that the imported files have the same name as the CSV file? Is this possible?

Re: Importing multiple csv files

Tom — Wed, 18 Jan 2023 17:47:05 GMT

How do I modify it, so that the imported files have the same name as the CSV file? Is this possible?

Only if the names of the CSV files are valid SAS dataset names. For example if the CSV file is named "class.csv" then you could pull out the "class" part and use it as the dataset name. But if the CSV file was named "gobbledy-gook.csv" then you could not use "gobbledy-gook" as the dataset names because dataset names cannot have a hyphen. Or if the names is longer than 32 bytes you cannot use it as a dataset name. You could use NVALID() function to test.

Note it is probably going to be a lot easier to just use normal SAS code to get the list of filenames instead of using the macro code you posted. It is waaaaaaaay easier to debug data steps than macro code. You can use CALL EXECUTE() to generate the PROC IMPORT code if you want.

%macro drive(dir,ext,dataset);
data &dataset ;
  length dataset $32 filename $256 ;
  rc = filename('mydir',symget('dir'));
  did = dopen('mydir');
  do fnum=1 to dnum(did);
    filename=dread(did,fnum);
    if lowcase("&ext")=lowcase(scan(filename,-1,'.') then do;
      dataset=scan(filename,-2,'./\');
      if not nvalid(dataset) then dataset=cats('csv',fnum);
      filename=catx('/',symget('dir'),filename);
      call execute(catx(' ','proc import dbms=csv'
                ,'datafile=',quote(trim(filename))
                ,'out=',dataset,'replace'
                ,';run;'
      ));
      output;
    end;
  end;
  did = dclose(did);
  rc = filename('mydir');
  keep dataset filename
run;
%mend drive;

Re: Importing multiple csv files

Ksharp — Thu, 19 Jan 2023 11:46:13 GMT

               proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=%qscan(%qsysfunc(dread(&did,&i)),1,.)