Qfyilyi

I have a directory in which every week there is a new file created. names are like below:

file_w1.csv

file_w2.csv

file_w3.csv

What I need to do is pick up the latest file (based on modified date), then parse the 2 characters just before the file extension.
So in this case, I want 'w3' because I want to use this to know which week I am reporting for.

How can I do this in SAS?

An operating system independent technique would use SAS External File functions such as dopen, fopen and finfo to obtain information about a folder and it's items.

Consider this sample code that does a 'full dump' of available information whilst parsing C:Temp on a Windows machine:

data _null_;

  length dfileref fileref $8 folder $200;



  rc = filename (dfileref, 'C:Temp');



  did = dopen(dfileref);

  if did then do;

    do index = 1 to doptnum(did);

      featurename = doptname(did,index);  

      featurevalue = dinfo(did,featurename);

      put index= featurename= featurevalue=;

      if featurename = 'Directory' then folder = featurevalue;

    end; 



    do dindex = 1 to dnum(did);

      entryname = dread(did,dindex);

      put dindex= entryname=;



      rc = filename(fileref, cats(folder, '/', entryname));



      fid = fopen (fileref); * if entry is another folder fid will be 0;

      if fid then do;

        do findex = 1 to foptnum(fid);

          featurename = foptname(fid, findex);

          featurevalue = finfo(fid, featurename);

          put +2 findex= featurename= featurevalue=;

        end;

        fid = fclose(fid);

      end;



      rc = filename(fileref);

    end;



    did = dclose(did);

  end;



  rc = filename (dfileref);

run;

After examining the log you can pare down the code needed to gather specific desired information into a data set. You can then use SQL queries to further act upon the data:

data csv_files(keep=fullname lastmod where=(fullname like '%.csv'));

  length dfileref fileref $8 folder $200;



  folder = 'C:Temp';

  rc = filename (dfileref, folder);



  did = dopen(dfileref);

  if did then do;

    do dindex = 1 to dnum(did);

      entryname = dread(did,dindex);

      rc = filename(fileref, cats(folder, '/', entryname));



      fid = fopen (fileref);

      if fid then do;

        fullname = finfo(fid,'Filename');

        lastmod = input(finfo(fid,'Last Modified'), datetime18.); format lastmod datetime18.;

        output;

        fid = fclose(fid);

      end;

      rc = filename(fileref);

    end;

    did = dclose(did);

  end;

  rc = filename (dfileref);

run;



proc sql;

  create table csv_newest as 

  select *, scan(scan(fullname,-1,'_'),1,'.') as tag

  from csv_files

  where prxmatch ('/_.+.csv$/', fullname)

  having lastmod = max(lastmod)

  ;