BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jgeslois
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

You can automate that step.

SAS can generate a list of files in a folder and find the most recent one. 


See a few different options here.

https://communities.sas.com/t5/SAS-Programming/How-to-List-all-the-files-in-a-folder/td-p/674065

 

Then use that filename in your process.

 


@jgeslois wrote:

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. 


 

View solution in original post

1 REPLY 1
Reeza
Super User

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.

You can automate that step.

SAS can generate a list of files in a folder and find the most recent one. 


See a few different options here.

https://communities.sas.com/t5/SAS-Programming/How-to-List-all-the-files-in-a-folder/td-p/674065

 

Then use that filename in your process.

 


@jgeslois wrote:

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. 


 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1141 views
  • 0 likes
  • 2 in conversation