How to query folders named by update date

[atbuk]

I need to query many address files to get a 5 year address history for a list of individuals. These SAS data files are organized in a folder structure when the folder name is based on when that file was last updated, e.g. d230821 for file updated 21 August  2023. In this folder would be a SAS data set that has the current addresses as of that update date for the individuals. For each individual, I need to get all address for a 5 year period relative to their birth date. Is there a way to create a macro to query these folders so that each folder does not need to explicitly linked to with a libname statement. I was thinking maybe something with the files metadata where I can compare the update date (in the folder name) with the individual's birthdate and only query that folder if it is within that 5 year window. There are 225 folders which is why I would like to come up with some sort of automated code. 

Hopefully this all makes sense. If not, please let me know and I will do my best to clarify.

TIA!

[PaigeMiller]

For each individual, I need to get all address for a 5 year period relative to their birth date.

Do you mean you need to loop through all folders (that's 5*365 = 1825 folders, give or take a few leap year days) in the five year period and read the SAS data set in each folder? Or do you mean you need to loop through folders 21 August 2023, 21 August 2022, 21 August 2021 and so on for five years? Or do you mean something else?

[atbuk]

Sorry - the folders are updated approximately monthly, but not on the same day of the month. They date back to 2005, so approx. 12 folders per year. I need to loop through the folders and read the SAS data.

[Reeza]

Use this to get the list of files. 

https://github.com/sasutils/macros/blob/master/dirtree.sas

From that data set extract out the date from the folder names.

Then process only what is needed.

[Tom]

You have to have a libref to reference a dataset.  But you can let SAS make one for you by using a quoted physical name instead of LIBREF.MEMNAME syntax.

data want;
  set "/topnode/date1/myfile.sas7bdat" "/topnode/date2/myfile.sas7bdat" "/topnode/date2/myfile.sas7bdat" ;
run;

Get the list of dataset files you want and then use that to generate the code.

data _null_;
  set files end=eof;
  if _n_=1 then call execute('date want; set ');
  call execute(quote(trim(filename))||' ');
  if eof then call execute(';run;');
run;

[Patrick]

Do the tables in the dated folders have always the same name or do they also have a date component? If a dated component then what's the naming pattern?

What's the OS? Unix/Linux or Windows?

Do you have option XCMD set (or is it NOXCMD)?

[atbuk]

All of the tables in the dated folders have the same name. I am on Windows. I am not familiar with XCMD.

[Patrick]

---

@atbuk wrote:
All of the tables in the dated folders have the same name. I am on Windows. I am not familiar with XCMD.

---

OK. You most likely need one single table (or view) with all the address information so you can join it to your other table (or do a hash lookup). 

The following code provides an approach how to create such a single table. For below code to work as-is folder c:\temp\_test must pre-exist.

1. Create sample data with same named table in monthly folders. 

/* create sample folders and tables */
%macro create_sample_data(
  folder_root=%sysfunc(pathname(work))
  ,table=addresses
  ,months=60
  );
  %local sv_dlcreatedir;
  %let sv_dlcreatedir=%sysfunc(getoption(dlcreatedir));
  options dlcreatedir;
  %put &=sv_dlcreatedir;
  %do i=1 %to &months-1;
    data _null_;
      folder_dt=intnx('month',today(),1-&i,'e');
      dated_folder=cats('d',put(folder_dt,mmddyy6.));
      call symputx('dated_folder',dated_folder,'l');
    run;
    /* create folder */
    libname _tmp "&folder_root/&dated_folder";
    /* create table in folder */
    data _tmp.&table;
      format addr_date date9.;
      addr_date=intnx('month',today(),1-&i,'e');
      address='blah';
    run;
    libname _tmp clear;
  %end;
  options &sv_dlcreatedir;
%mend;
%create_sample_data(folder_root=c:\temp\_test );

2. Create view over sample data

%macro create_sql_view(
  folder_root=%sysfunc(pathname(work))
  ,table=addresses
  ,months=60
  ,outview=work.test
  );

  /* get list of SAS files in monthly folders */
  data work.__sasfiles;
    do i=1 to &months;
      length dated_folder $7 fullpath $500;
      folder_dt=intnx('month',today(),1-i,'e');
      dated_folder=cats('d',put(folder_dt,mmddyy6.));
      fullpath=cats("&folder_root\",dated_folder,"\&table..sas7bdat");
      if fileexist(fullpath)=1 then 
        do;
          date_key=intnx('month',folder_dt,0,'b');
          output;
        end;
    end;
  run;

  /* create sql view over SAS files */
  filename codegen temp;
  data _null_; file codegen; run;

  data _null_;
    file codegen;
/*    file print;*/

    set work.__sasfiles end=_last;

    if _n_=1 then
      do;
        put
          "  proc sql;" /
          "    create view &outview as" /
          ;
        end;

    put
      "    select * ," date_key "as date_key format=date9.,'" fullpath +(-1) "' as fullpath" /
      "    from '" fullpath +(-1) "'" /
      ;

    if not _last then
      do;
        put '    union all corr';
      end;
    else
    if _last then
      do;
        put
          "    ;" /
          "  quit;" 
          ;
      end;
  run;

  %include codegen / source2;
  filename codegen clear;

  /*  housekeeping */
  proc datasets lib=work nolist nowarn;
    delete __sasfiles;
  quit;

%mend;
%create_sql_view(folder_root=c:\temp\_test, months=60, outview=work.test);

And here the result you'll get with above code.

/* print view */
proc print data=work.test;
run;