SAS - read multiple CSV files and create a dataset for each one of them

0
votes

I use this code to input CSV files data into SAS:

%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))"
             dbms=csv
             replace
             out=dsn&cnt 
           ;            
           run;         

         %end; 
       %end;      
     %end;
   %end;
   %else
     %put &dir cannot be open.;

   %if &did %then %let rc=%sysfunc(dclose(&did));      

 %mend drive;

%drive(C:path\to\folder\,csv)

but it names the datasets as dsn1, dsn2 etc while I want to have the name of the file (without the CSV part) as the dataset name. How can I do that?

2
sas
Are you sure the filenames will work as dataset names? SAS dataset names are limited to 32 characters and must follow SAS naming rules. – Tom
Does that code even work? You are using %superq() function wrong. It wants the name of the macro variable to quote, not the value to quote. – Tom
@Tom code is originally from here and it does work documentation.sas.com/… – Reeza
@Reeza SAS posts a lot of poorly written code because they are just intended to emphasize a point. If you call that code with EXT=csv then the %superq(&ext) expression is going to quote the value of the undefined CSV macro variable. – Tom

2 Answers

0
votes

I think problem occur cause of macro call. Change folder to correct one, like :

%drive(C:\temp\,csv)

UPDATE(solving the problem of naming the datasets):

%macro read(dir,ext);%macro d; %mend d;
filename filelist pipe "dir /b &dir*.&ext"; 

data want;
  infile filelist dlm="¬";
  length cmd_line $2000;
  input cmd_line;
run;

proc sql noprint;
   select tranwrd(cmd_line,".&ext",'') into:dataset_list separated by ' ' from want;
quit;

%do i=1 %to %sysfunc(countw(&dataset_list));
   %let dataset_name = %scan(&dataset_list,&i,%str( ));
          proc import datafile="&dir.&dataset_name..&ext" out=&dataset_name
           dbms=csv replace;            
          run;
%end;
filename filelist clear;
%mend read;

%read(c:\temp\,csv);
0
votes

For the innards you want to deal with the directory member name retrieved with dread, instead of dealing with the i'th processed file, as coded by ds&cnt.

FIND the position of the dot (.) in the filename and separate out the name and extension parts. After extracting the name part of the filename, compress out all non SAS name characters.

 %do i=1 %to &memcnt;              

   %let filename = %qsysfunc(dread(&did,&i));

   %let pindex = %sysfunc(FIND(&filename,.,-%length(&filename)));

   %if &pindex > 1 %then %do;
     %let extpart = %upcase(%substr(&filename,&pindex));
     %let namepart = %substr(&filename,1, &pindex);

     %* keep only characters that are valid for a sas name (normalize the namepart);
     %let sasname = %sysfunc(compress(&namepart,,kn));

     %if &extpart = CSV %then %do;

       proc import 
         datafile="&dir\&filename"
         dbms=csv
         replace
         out=work.&sasname
       ;            
       run;         

     %end;
   %end;
 %end;

If the filenames are too long the generated IMPORT will ERROR. Be sure to coordinate with data providers so you get

  • right lengthed filenames, and
  • filenames that do not collide when normalized