topic Re: output macro variable into dataset under nested loop in SAS Programming

output macro variable into dataset under nested loop

Tushar — Thu, 02 May 2019 07:58:52 GMT

Hi All,

from below code I want to create dataset which will have this two column path and lname which gate value from this two macro variable &dir and &name respective. can you please help me to put my output statement in right position so that I can create data set ?

%macro drive(dir,ext);                                                                                                                  
  %local filrf rc did memcnt name i;                                                                                                    
                                                                                                                                        
  /* Assigns a fileref to the directory and opens the directory */                                                           
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                               
  %let did=%sysfunc(dopen(&filrf));                                                                                                     
                                                                                                                                        
  /* Make sure directory can be open */                                                                                                 
  %if &did eq 0 %then %do;                                                                                                              
   %put Directory &dir cannot be open or does not exist;                                                                                
   %return;                                                                                                                             
  %end;                                                                                                                                 
                                                                                                                                        
   /* Loops through entire directory */                                                                                                 
   %do i = 1 %to %sysfunc(dnum(&did));                                                                                                  
                                                                                                                                        
     /* Retrieve name of each file */                                                                                                   
     %let name=%qsysfunc(dread(&did,&i));                                                                                               
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                   
     /* If condition is true print the full name to the log        */                                                                   
      %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do;                                                                       
        %put &dir\&name;                                                                                                                
      %end;                                                                                                                             
     /* If directory name call macro again */                                                                                           
      %else %if %qscan(&name,2,.) = %then %do;                                                                                          
        %drive(&dir\%unquote(&name),&ext)                                                                                               
      %end;                                                                                                                             
                                                                                                                                        
   %end;                                                                                                                                
                                                                                                                                        
  /* Closes the directory and clear the fileref */                                                                                      
  %let rc=%sysfunc(dclose(&did));                                                                                                       
  %let rc=%sysfunc(filename(filrf));                                                                                                    
                                                                                                                                        
%mend drive;                                                                                                                            
                                                                                                                                        
/* First parameter is the directory of where your files are stored. */                                                                  
/* Second parameter is the extension you are looking for.           */                                                                  
%drive(c:\temp,sas)

Re: output macro variable into dataset under nested loop

ChrisNZ — Fri, 03 May 2019 05:22:54 GMT

Sadly SAS still doesn't have write functions to data sets.

So you need something like this:

%macro drive(dir,ext);                                                                                                                  
  %local filrf rc did memcnt name i;                                                                                                    
                                                                                                                                        
  /* Assigns a fileref to the directory and opens the directory */                                                           
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                               
  %let did=%sysfunc(dopen(&filrf));                                                                                                     
                                                                                                                                        
  /* Make sure directory can be open */                                                                                                 
  %if &did eq 0 %then %do;                                                                                                              
   %put Directory &dir cannot be open or does not exist;                                                                                
   %return;                                                                                                                             
  %end;                                                                                                                                 
                          
data OUT; 
length DIR NAME $200;
                                                                                                              
   /* Loops through entire directory */                                                                                                 
   %do i = 1 %to %sysfunc(dnum(&did));                                                                                                  
                                                                                                                                        
     /* Retrieve name of each file */                                                                                                   
     %let name=%qsysfunc(dread(&did,&i));                                                                                               
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                   
     /* If condition is true print the full name to the log        */                                                                   
      %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do;                                                                       
        %put &dir\&name;      

DIR="&dir"; NAME="&name"; output;
                                                                                                          
      %end;                                                                                                                             
     /* If directory name call macro again */                                                                                           
      %else %if %qscan(&name,2,.) = %then %do;                                                                                          
        %drive(&dir\%unquote(&name),&ext)                                                                                               
      %end;                                                                                                                             
                                                                                                                                        
   %end;                  

run;                                                                                                              
                                                                                                                                        
  /* Closes the directory and clear the fileref */                                                                                      
  %let rc=%sysfunc(dclose(&did));                                                                                                       
  %let rc=%sysfunc(filename(filrf));                                                                                                    
                                                                                                                                        
%mend drive;

Re: output macro variable into dataset under nested loop

ScottBass — Fri, 03 May 2019 06:17:56 GMT

This doesn't do exactly what you want, as it does not support full recursion. Perhaps someone smarter than me (@ChrisNZ?) can get recursion working.

However, this does one-level recursion:

proc delete data=filelist;
run;
%dirlist(dir=C:\Temp,type=d,data=dirlist)
%macro code;
   %let fullname=%trim(&fullname);
   %dirlist(dir=&fullname,data=temp,filter=ext='sas');
   proc append base=filelist data=temp;
   run;
%mend;
%loop_control(control=dirlist);

See:

https://github.com/scottbass/SAS/blob/master/Macro/dirlist.sas

https://github.com/scottbass/SAS/blob/master/Macro/loop.sas

https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas

https://github.com/scottbass/SAS/blob/master/Macro/parmv.sas

Also, I think you'd get much better performance if you did most of this code in the data step rather than macro.

Something like:

%macro drive(dir,ext);  
   data dirlist;
      rc=filename("dirlist","&dir");
      did=dopen("dirlist");
      do i=1 to dnum(did);
         name=dread(did,i);
         if scan(name,-1,'.')="&ext" then output;
      end;
      rc=dclose(did);
      rc=filename("dirlist");

      * perhaps implement recursion with link/return statements? ;
   run;
%mend; 
%drive(C:\Temp,sas);

The only macro "bits" are the parameters for file path and desired extension - everything else is a data step. If you're looping via macro, that will likely be orders of magnitude slower than the data step.

@ChrisNZ if you do get recursion working, and it can be added to my dirlist macro, that would be great! I just haven't bothered with that for now.

Re: output macro variable into dataset under nested loop

ScottBass — Fri, 03 May 2019 07:00:07 GMT

Also, do you have ALLOWXCMD available in your environment?

What happens if you run this code (adjust if you're running Linux):

filename dirlist pipe "dir /b /s \\sascs\linkage\RL_content_snapshots";
data dirlist1;
   infile dirlist lrecl=256 truncover;
   input;
   filename=_infile_;
run;

* or ;

filename dirlist pipe "powershell ""Get-ChildItem -recurse \\sascs\linkage\RL_content_snapshots | ForEach-Object {$_.Fullname}"" ";
data dirlist2;
   infile dirlist lrecl=256 truncover;
   input;
   filename=_infile_;
run;