CODEWITHSUNDEEP
importautomationmacrossas
cartoom02
cartoom02

Reputation: 89

SAS Cycle through and import each file. Combining two sets of code

I have 2 sets of SAS code that both work correctly.

One is a macro that can cycle through a directory and find all the files thzat match a particular extension.

The other is a file that can open one of the zip files and import it into a table.

I will like to combine the two sets of codes and be able to cycle through the files import them all into a SAS dataset. The dataset can be number 1 - (number of files).

I'ved tried to add the code into the macro directly but am having trouble with directories that have a space and all the code executing correctly.

Code 1:

%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 open.;
  %let rc=%sysfunc(dclose(&did));      

 %mend drive;

%drive(/opt/sasdata/mnt/FM/folder,Z) ;

Code 2:

Filename Comgzip Pipe "gzip -cd /opt/sasdata/mnt/FM/folder/20181218.Z";
Data DataSet15;
Infile ComgZip  dsd dlm=',' missover firstobs=1;

Input 
ContractID $
ProdID $
IssueDate $
Market $
TaxClsCode $
BenEffDate $
PriceDate $
AnnuitDOB $
AnnuitAge
BenYear
BenMonth ;
run;

Upvotes: 1

Views: 144

Answers (2)

Tom
Tom

Reputation: 51566

Since you are going to generate code there is no need to use such a complex macro for finding the files. That first macro is overly complex because it is using macro code to call SAS functions. Use something that just uses SAS code (instead of macro code) and generates a dataset of the files found.

%macro files(dir,ext,dsn=files);
filename mydir "&dir";
data &dsn ;
  length name $256 ;
  did=dopen('mydir');
  if did then do;   
    do i=1 to dnum(did);              
      name="&dir\"||dread(did,i);
      if upcase(scan(name,-1,'.'))=%upcase("&ext") then output;
    end;
    rc=dclose(did);
  end;
  keep name;
run;
filename mydir;
%mend files;

Then use the dataset of file names to generate code to import each file. Either directly generate the code to import each file, or create a simple macro that imports one file and generate calls to that macro.

The issue with spaces in the filenames is probably impacting the FILENAME statement in your second code snippet. Just add more quotes.

%macro readfile(filename,dsn);
filename comgzip pipe "gzip -cd ""&filename"" ";
data &dsn;
  infile comgzip dsd truncover;
  input 
    ContractID $
    ProdID $
    IssueDate $
    Market $
    TaxClsCode $
    BenEffDate $
    PriceDate $
    AnnuitDOB $
    AnnuitAge
    BenYear
    BenMonth 
  ;
run;
%mend readfile;

So now you can generate calls with something like this:

%files(/opt/sasdata/mnt/FM/folder,Z) ;
data _null_;
  set files;
  n+1;
  call execute(cats('%readfile(',name,',',cats('dsn',n),')'));
run;

Upvotes: 2

Robert Penridge
Robert Penridge

Reputation: 8513

Don't have SAS in front of me right now to test this but my guess is that your problem is coming from the lack of quotes around the pathnames. Specifically the filename statement:

Filename Comgzip Pipe "gzip -cd /opt/sasdata/mnt/FM/folder/20181218.Z";

Let's assume that your pathname has a space and looks like this:

/opt/sasdata/mnt/FM/my folder/20181218.Z

You would then want the code produced by your macro code to look something like this (so that the file pathname is wrapped in double quotes, and the whole command is wrapped in single quotes):

Filename Comgzip Pipe 'gzip -cd "/opt/sasdata/mnt/FM/my folder/20181218.Z" ';

The problem then becomes, how do you get your macro variables to resolve when surrounded by single quotes. You can do this simply by using the %str(%') notation. This syntax simply resolves to a single quote, so you can place these at either end of the code containing double quotes that you still want resolved and it should fix the issue. The final line of code would look something like this:

Filename Comgzip Pipe %str(%')gzip -cd "&full_path_and_filename" %str(%');

Upvotes: 0

Related Questions