Proc Import for Batch CSVs - Naming the Imported Files

[eer_seer]

Friends,

The following code works beautifully for batch importing CSV files. I'm asking for a basic tweak.  I would like the imported sas file to have the same name as the actual csv being imported. I've been playing around with the "out=dsn&cnt" command line, but can't seem to find the right logic.  Suggestions?

%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:\temp,csv)

[Tom]

Why are using the DOPEN(), DREAD() functions in macro code instead of data step?  Your macro is already generating SAS steps so there is no need to complicate it by using macro code to handle the filename data.

%macro drive(dir,ext); 
data files ;
  length filename $256 memname $32 ;
  rc=filename('mydir',symget('dir'));
  did=dopen('mydir');
  do i=1 to dnum(did);
    filename=dread(did,i);
    if symget('ext')=scan(filename,-1,'.') then do;
      memname = scan(filename,1,'.');
      output;
      call execute(cats(' '
         ,'proc import datafile='
         ,quote(catx('\',symget('dir'),filename))
         ,'dbms=csv'
         ,'out=',memname,'replace'
         ,';run;'
      ));
    end;
  end;
  rc=dclose(did);
  rc=filename('mydir');
run;
%mend drive ;

What if the name of the CSV is not valid to be used as the name of a dataset?

[eer_seer]

Thank you.  I took the code straight out of the sas documentation?

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

[Tom]

The page you linked is from the SAS Macro Language documentation, which explains the overuse of macro code.

SAS frequently does not share very good code.  Just look at the gibberish data steps that PROC IMPORT will generate for all of those CSV files.

[eer_seer]

I appreciate you engaging me.

I'm not sure what I need to put into your suggested code to make it work.  Where do I put my directory I want SAS to read into your code?

[Reeza]

Try replacing this:

          proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 
             dbms=csv replace;    

With:

          proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=%qsysfunc(dread(&did,&i))
             dbms=csv replace;    

From your original code.

[eer_seer]

NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
ERROR: Libref 'ef2009d_rv' exceeds 8 characters.
NOTE: The SAS System stopped processing this step because of errors.
ef2010d_rv.csv


NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Those are the errors I get when trying your suggestion to replace the original proc import with below:

 proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=%qsysfunc(dread(&did,&i))
             dbms=csv replace;   

[Reeza]

Show more of the log, specifically the line above that will show what the macro variable resolves to.
You may be running into the issue Tom mentioned, what if the CSV file doesn't have a valid SAS name. SAS names must be 32 characters or less, cannot start with a number, and can only contain alphanumeric characters and underscores. You can get around this in a few ways but it usually ends up causing issues later on. What are you planning to do with all of these files? If you need to combine them via append in the end, you can actually read them all in a single non-macro data step.

[Tom]

The replacement macro takes the same inputs as the example macro you posted in your question. So call it the same way.

%drive(c:\temp,csv)