Integrate Folder scanning code with proc import for multiple subfolders

[imdickson]

Hi Guys, I have 2 sets of codes.

Code 1: Scan all subfolders in a directory and detect all files that are in xls extension.

In log window, i can see that it print out all the files in xls extension in multiple subfolders.

Objective: To be able to read each and every file and process all xls file that i prepared in code 2.

Code 2: PROC IMPORT to open and process just 1 specific file. The process includes transpose data, filtering, rename column and etc.

Objective: To be able to process/import all files that it scanned from code 1.

Code 1-Scan all subfolders for xls file extension

%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;
        <I put the proc import statement here. However, my data step has macro. I am thinking on how to put the macro here.>>                                                                                                                
      %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(/data/source/tttt/Files,xls)

Code 2- 
PROC IMPORT OUT= WORK.excelout DATAFILE= "/data/source/tttt/Files/Central/ABBA/Central_ABBA_11_201401.xls" 


            DBMS=csv REPLACE;
			delimiter="	";
getnames=no;
RUN;
 
proc contents data=excelout noprint out=data_info /*(keep = name varnum)*/;
run;
 
data _null_;
	set data_info;
	call symputx(compress("col"||VARNUM),compress(NAME));
	call symputx("cnt",_n_);
run;


and etc for all other processing. Not posting for now as it is really long

As you can see, the 2 seperate code above is doing seperate task at this moment. I want to make Code 2 to have the ability of scanning through all subfolders for xls files and then process it in Code 2.

Does anyone know if this is achievable?

[imdickson]

Hi guys, as you all can see, i can put my proc import statement so that i will out=&dir/&name but after this proc import statement, I have a macro for processing the data that i got from proc import statement. Can i lump everything(macro) under that clause?

 Or do i have to write the macro(process data step) outside of the macro(scan file)?

[ballardw]

---

@imdickson wrote:

Hi Guys, I have 2 sets of codes.

Code 1: Scan all subfolders in a directory and detect all files that are in xls extension.

In log window, i can see that it print out all the files in xls extension in multiple subfolders.

Objective: To be able to read each and every file and process all xls file that i prepared in code 2.

Code 2: PROC IMPORT to open and process just 1 specific file. The process includes transpose data, filtering, rename column and etc.

Objective: To be able to process/import all files that it scanned from code 1.

Code 1-Scan all subfolders for xls file extension

%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;
        <I put the proc import statement here. However, my data step has macro. I am thinking on how to put the macro here.>>                                                                                                                
      %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(/data/source/tttt/Files,xls)

Code 2- 
PROC IMPORT OUT= WORK.excelout DATAFILE= "/data/source/tttt/Files/Central/ABBA/Central_ABBA_11_201401.xls" 


            DBMS=csv REPLACE;
			delimiter="	";
getnames=no;
RUN;
 
proc contents data=excelout noprint out=data_info /*(keep = name varnum)*/;
run;
 
data _null_;
	set data_info;
	call symputx(compress("col"||VARNUM),compress(NAME));
	call symputx("cnt",_n_);
run;


and etc for all other processing. Not posting for now as it is really long

As you can see, the 2 seperate code above is doing seperate task at this moment. I want to make Code 2 to have the ability of scanning through all subfolders for xls files and then process it in Code 2.

Does anyone know if this is achievable?

---

Yes it is achievable

First, you need to know the differences between importing CSV and XLS. And are they actually XLS?

Second, any process that relies on proc import is chancy for repeated files at best. The procedure has to guess for each and every file what the lengths of variables are, whether they are numeric or character and what informat/formats to associate if dates or currency values are numeric. It is very likely that if any of these files are to be combined at a later point in time that lengths of character variables are different with potential lose of text or will not combine at all because variables have different types in different data sets.