sas import latest csv file in folder

Question

I have a folder that that has a file added to it each day as below

Z:\old\stock110813.csv Z:\old\stock120813.csv Z:\old\stock130813.csv Z:\old\stock140813.csv

1. I would like to import the latest file into SAS dynamically ie. searches the folder for the latest date
2. or would like sas to make a copy of the latest and change the name & location of the file

I have been searching the web for days testing little bits of code but am struggling therefore any help would be appreciated.

cheers John

Answer 1

If the date is predictable (ie, today's date) then you can do:

%let date=%sysfunc(today,dateformat.); *whatever dateformat you need;
proc import file="z:\old\stock&date..csv"... ;
run;

If not, then your best bet is to use a pipe with the directory listing to see the most recent filename. Something like this (directory logic depends on your server/OS/etc.)

filename mydir pipe 'dir /b /od /a-d z:\old\';

data myfiles;
infile mydir;
input @1 filename $32.;
call symput('filename',filename);
run;

proc import file="z:\old\&filename." ... ;

Answer 2

Do you want to use the system date or the date in the filename to determine what's the newest file? If you want to use the create of modify date, check out the foptname function to determine them. This code looks at the date in the filename. This code works without the need for X command authorization.

data newestFile (keep=newestFile);
 format newestFile $20.;
 retain newestFile newestDate;
 rc = filename("dir","z:\old\");
 did = dopen("dir");
 /* loop through file and subdirectories in a directory */
 do i = 1 to dnum(did); 
  csvFile = dread(did,i);   
  rc=filename("fid",cats("z:\old\",csvFile));
  sdid=dopen("fid");
  /*check if date in name is newest if it is a file  */
  if sdid le 0 then do;
   csvFileDate = input(substr(csvFile,6,6),ddmmyy6.);
   if csvFileDate gt newestDate then do;
    newestDate = csvFileDate;
    newestFile = csvFile;
   end;
  end;
 else rc = dclose(sdid);
 end;
 rc = dclose(did);
 /* move and rename file with latest date to newestFile.csv */
 rc = rename(cats("z:\old\",newestFile), "z:\new\newestFile.csv",'file');
run;