topic Re: use original filename while importing using macro in SAS Data Management

use original filename while importing using macro

april_cz — Thu, 25 Jul 2019 01:52:50 GMT

Hi,

I am trying to import all csv files under a folder using macro. I want to keep the original file name. (for example: original file name: data11.csv and the imported sas data file name: data11.sasdata .

I find following macro program can import the files, my question is how can modify it to retain the original file name rather than generate a new name.

The macro I find on line:

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en

%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 /*this is the part I need to change*/
           dbms=csv replace;            
          run;          
       %end; 
      %end;  

    %end;
      %end;
  %else %put &dir cannot be open.;
  %let rc=%sysfunc(dclose(&did));      
             
 %mend drive;
 
%drive(c:\temp,csv)

Re: use original filename while importing using macro

VDD — Thu, 25 Jul 2019 01:44:47 GMT

data11.csv is not a valid SAS table name.

Re: use original filename while importing using macro

april_cz — Thu, 25 Jul 2019 01:49:57 GMT

I am not using .csv for sas file.

What I am trying to do is converting csv file into sas file.

After converting.

data11.csv becomes data11.sasdata

Re: use original filename while importing using macro

Patrick — Thu, 25 Jul 2019 08:55:51 GMT

If I read the macro code you've posted right then %qsysfunc(dread(&did,&i)) will return the source file name with suffix.

You just need to parse out the name without suffix. Below code is untested and will only work for source file names without a second dot in the name.

%let name2=%qscan(%qsysfunc(dread(&did,&i)),-2,.);

So you could change your code to something like:

.....out=work.&name2

But: This will only work if your external files comply with SAS naming standards.

Else you will either need something ugly like:

.....out=work."%sysfunc(substrn(&name2,1,32))"n

...or you need to pre-process &name and ensure/convert it to a SAS compliant table name.

Alternatively add the source file name as label

.....out=dsn&cnt(label="&name2")

....or with suffix included

.....out=dsn&cnt(label="%qsysfunc(dread(&did,&i))")