I have a folder with several excel files that are generated and are saved from a particular code that I run on that particular day I work with it. Example:
- document042922.xlsx was generated and saved in this folder on today (4-29-2022)
- document042522.xlsx was generated and saved in this folder on (4-25-2022)
- etc.
So using this code I'm working with, it will generate an xlsx file at a certain step, and its file name will be named and then saved in that folder with today's date. Example: document042922.xlsx for today. After this step, what I need to do is to take today's file and compare the values and do other calculations using the immediate prior day's exported file that is in the same saved folder. However, the prior file, may not be the immediate day before it (yesterday) and could vary in terms of when it was done. It could have been yesterday's date (4/28), it could be 3 days ago date (4/25) or another past date etc. It will always be looking at a prior date. And there's no way to know for sure, outside of going and manually sorting the folder to find what the last prior day was to begin the next step of the process. The date does need to be the immediate last one done. So if today is 4/29, and the last file in there was 4/28, it would need to select the 4/28 file. And if I do this process again on 4/30, 5/1, and 5/4, when I do it on 5/4 it would need to find the immediate last one of 5/1, so it would have to be variable to how many days may be between each of the files' dates.
So in my code, to export the current today's file would look like this:
proc export data=document
outfile="Z:\filepath\document %sysfunc(putn(&today.,MMDDYYD.)).xlsx"
DBMS=Excel REPLACE;
run;
Then I need to import that file, as well as the prior one.
*for today's file;
data document;
set document;
run;
*for prior file - i'm not sure how to do this;
%let prior_date = *unknown;
data WORK.document%sysfunc(putn(&today.,mmddyyn6.));
infile 'Z:\filepath\document042822.csv'
Run;
Thank you for any help you are able to give, I appreciate it.