topic Re: Proc Import for Batch CSVs - Naming the Imported Files in SAS Programming

Proc Import for Batch CSVs - Naming the Imported Files

eer_seer — Fri, 23 Apr 2021 14:44:49 GMT

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)

Re: Proc Import for Batch CSVs - Naming the Imported Files

Tom — Fri, 23 Apr 2021 15:00:26 GMT

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?

Re: Proc Import for Batch CSVs - Naming the Imported Files

eer_seer — Fri, 23 Apr 2021 15:18:37 GMT

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

Re: Proc Import for Batch CSVs - Naming the Imported Files

Tom — Fri, 23 Apr 2021 15:42:23 GMT

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.

Re: Proc Import for Batch CSVs - Naming the Imported Files

eer_seer — Fri, 23 Apr 2021 15:31:00 GMT

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?

Re: Proc Import for Batch CSVs - Naming the Imported Files

Reeza — Fri, 23 Apr 2021 15:40:46 GMT

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.

Re: Proc Import for Batch CSVs - Naming the Imported Files

Tom — Fri, 23 Apr 2021 15:37:58 GMT

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)

Re: Proc Import for Batch CSVs - Naming the Imported Files

eer_seer — Fri, 23 Apr 2021 15:46:56 GMT

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;

Re: Proc Import for Batch CSVs - Naming the Imported Files

Reeza — Fri, 23 Apr 2021 15:57:56 GMT

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.