topic Re: Populating data set from Filename dirlist pipe "find tmpFolder" in SAS Programming

Populating data set from Filename dirlist pipe "find tmpFolder"

RichM — Wed, 21 Jun 2017 23:29:21 GMT

I'm running in linux and I'm trying to parse a number of rows formatted as follows (file name and directory structure will be variable)

06/16/17 09:40:19.8344320410 /workspace/alphabuilder/data-jp/D160125.DB

The goal from this example is a data set where each record includes

Date = 06/16/17

Time =09:40:19.83

File = D160125.DB

Folder = /workspace/alphabuilder/data-jp/

My Sas code

/*Test varaibles*/

%let folder = /workspace/uat/card2/ai/x796785/V_drive/alphabuilder/data-jp/ ;
%let prefix = M ;
%let suffix = db ;

Filename dirlist pipe "find &folder. -iname &prefix.*&suffix. -printf '%TD %TT %p\n'";

Data &outlist (keep=folder filename date time);
       Infile dirlist end=last;
       format date mmddyy10. time time8.;
       input;

       *Create the folder= file directory location;
       date = input(_infile_,mmddyy10.);
  time = input(substr(_infile_,10,18),time8.);
  filename = substr(_infile_,length(_infile_) - index(reverse(_infile_),"/") + 2,length(_infile_));
  folder = substr(_infile_,29,length(_infile_)- index(reverse(_infile_),"/") );

Run;

My problem:

Folder includes the filename at the end

/workspace/alphabuilder/data-jp/D160125.DB

I tried a number of variations including

folder = substr(_infile_,29,length(_infile_)- 5 );

folder = substr(_infile_,29,35 );

I'm sure this is a rookie issue but I haven't been able to google a solution.

Re: Populating data set from Filename dirlist pipe "find tmpFolder"

art297 — Wed, 21 Jun 2017 23:50:08 GMT

Assuming I correctly understand what you're trying to do:

data have;
  fullpath='/workspace/alphabuilder/data-jp/D160125.DB';
  call scan(fullpath, -1, position, length,'/');
  folder=substr(fullpath,1,position-1);
  filename=scan(fullpath,-1,'/');
run;

Art, CEO, AnalystFinder.com

Re: Populating data set from Filename dirlist pipe "find tmpFolder"

ChrisNZ — Thu, 22 Jun 2017 00:02:17 GMT

Like this?

 FOLDER=substr(_infile_,29,find(_infile_,'/',-999)-29);

Re: Populating data set from Filename dirlist pipe "find tmpFolder"

Reeza — Thu, 22 Jun 2017 03:13:17 GMT

There's also the FINFO approach.

http://support.sas.com/kb/40/934.html

/** Macro technique **/                                                                                                                 
%macro FileAttribs(filename);                                                                                                           
   %local rc fid fidc;                                                                                                                   
   %local Bytes CreateDT ModifyDT;                                                                                                       
   %let rc=%sysfunc(filename(onefile,&filename));                                                                                       
   %let fid=%sysfunc(fopen(&onefile));                                                                                                  
   %let Bytes=%sysfunc(finfo(&fid,File Size (bytes)));                                                                                  
   %let CreateDT=%qsysfunc(finfo(&fid,Create Time));                                                                                     
   %let ModifyDT=%qsysfunc(finfo(&fid,Last Modified));                                                                                   
   %let fidc=%sysfunc(fclose(&fid));                                                                                                    
   %let rc=%sysfunc(filename(onefile));                                                                                                 
   %put NOTE: File size of &filename is &Bytes bytes;                                                                                  
   %put NOTE- Created &CreateDT;                                                                                                       
   %put NOTE- Last modified &ModifyDT;                                                                                                 
%mend FileAttribs;                                                                                                                      
                                                                                                                                        
/** Just pass in the path and file name **/                                                                                             
%FileAttribs(c:\aaa.txt)                                                                                                                
                                                                                                                                        
                                                                                                                                        
/** Non-macro technique **/                                                                                                             
filename fileref 'c:\aaa.txt';                                                                                                          
data a(drop=fid);                                                                                                                       
   infile fileref truncover obs=1;                                                                                                       
   fid=fopen('fileref');                                                                                                                 
   Bytes=finfo(fid,'File Size (bytes)');                                                                                                 
   crdate=finfo(fid,'Create Time');                                                                                                      
   moddate=finfo(fid,'Last Modified');                                                                                                   
run;                                                                                                                                    
                                                                                                                                        
proc print;                                                                                                                             
run;

Re: Populating data set from Filename dirlist pipe "find tmpFolder"

Kurt_Bremser — Thu, 22 Jun 2017 08:18:28 GMT

I think you're overcomplicating things:

data want;
input date :mmddyy8. time :time20. pathstr :$100.;
format
  date mmddyy8.
  time time11.2
  file_name $50.
;
file_name = scan(pathstr,-1,'/');
drop pathstr;
cards;
06/16/17 09:40:19.8344320410 /workspace/alphabuilder/data-jp/D160125.DB
;
run;

It presumes that nobody fucked up by putting blanks into filenames; if that is a possibility, the initial read for pathstr would have to be done with a fixed format from a given position.