extracting data while skipping a day in between

[Solly7]

Hi i need help in extracting data using below set of rules/instructions.

•  I need to skip a day before the actual date of extracting the data  , for example todays date is "2022-01-17", so  the program should  extract data for process_date "2022-01-13", skipping the process_date "2022-01-14". Note that when we skip the dates we exclude or we do not take into consideration Saturday and Sunday, we only focus on weekdays(monday to friday). So in this instance the could should neglect saturday(2022-01-15) & sunday(2022-01-16) then skip the process_date "2022-01-14" and extract the data on process_date "2022-01-13"

• Again if i wanted to extract the data on date 2022-01-14, the code should skip the process_date "2022-13-01" and extract the data on the process_date "2022-01-12".

• Lastly, if we need to extract the data on date "2022-01-12", the code should run from process_date "2022-01-08" which is saturday & process_date "2022-01-09" which is Sunday up to "2022-01-10" which is monday because some transactions runs on a weekend(saturday and sunday)..kindly Note that this condition only applies if  the code is ran on wednesdays for monday process_date which is "2022-01-12" in this instance.

Kindly note that the reason for all of above is because I need to schedule the code to run automatically during weekdays(monday to friday), your help will be highly appreciated!

                                                      Data HAVE

data leave;
  input process_date :yymmdd10. amount reference &:$50.;
  format process_date yymmdd10.;
  datalines;
2022-01-08 100 trn01
2022-01-09 200 trn02
2022-01-10 300 trn03
2022-01-11 400 trn04
2021-01-12 500 trn05
2022-01-13 600 trn06
2022-01-14 700 trn07
2022-01-15 800 trn08
2022-01-16 900 trn09
2022-01-17 1000 trn10 

;

Data Want - for process date  "2022-01-13" which is thursday (First bullet point on the rules/instructions)

process_date   amount   reference

2022-01-13        600        trn06

Data Want - for process date  "2022-01-12" which is wednesday (Second bullet point on the rules/instructions)

process_date   amount   reference

2022-01-12        500        trn05

Data Want - for process date  "2022-01-10" which is Monday (3rd bullet point on the rules/instructions)

process_date   amount   reference

2022-01-08        100        trn01

2022-01-09        200        trn02

2022-01-10        300        trn03

[ballardw]

Extract the data from where?

Your description of "skip" does not quite align with what I think you are showing for describing for dates used. You appear to actually be using 3 dates on Mondays, not skipping anything. Please clarify the description.

Note that the WEEKDAY function returns the day of the week, 1 for Sunday  to 7 for Saturday, for a valid date value. So you can test the TODAY() function to see what day code is running. So given those two functions you should be able to create "process date" values.

[Solly7]

Hi, kindly note that if we need to run the code for mondays, which in this instance the process date is "2022-01-10", the code needs to be ran on wednesdays which is date "2022-01-12" in this instance, meaning that we have skipped tuesday which the process_date is "2022-01-11" in this instance.

For the process_date which falls on a monday, the code will have to extract the data from saturday to monday simply because on my side there are some transactions which are processed on weekend (saturday and sunday) and I need to include the in the report which was processed for monday.

To be more precise, mondays transactional data is ran on wednesdays
Tuesdays transactional data is ran on thursdays
Wednesdays transactional data is ran on fridays
Thursdays transactional data is ran on mondays
fridays transactional data is ran on tuesdays

[Kurt_Bremser]

So you want to go two workdays into the future? What about holidays that fall on a Monday-Friday?

[Solly7]

Hey Kurt, that is a very good question..we treat those holidays as weekend. We do not take them into consideration.

For example if today "2022-01-17" is a holiday, then we do not have to do any reporting for process_date "2022-01-17" as it will be treated as a holiday. But for simplicity sake we can just ignore the holidays and take care of only saturday and sunday, meaning those reports generated/automated during holidays I will not use them for my reporting. We can only add exceptions for saturday and sunday. Hope that helps Kurt

[Kurt_Bremser · Accepted solution]

I would do this:

data _null_;
rundate = ifn(weekday(today()) in (5,6),today() + 4,today() + 2);
call symputx('rundate',rundate);
run;

On Thursday and Friday, it jumps to Monday and Tuesday respectively, on Monday, Tuesday and Wednesday just two days ahead.

[Solly7]

Hi Kurt, thanks for the code as ALWAYS..but i still need hope in how to incorporate your code with my data below..how to use the process_date from my data onto the code you have provided

data transactions;
input process_date :yymmdd10. amount reference &:$50.;
format process_date yymmdd10.;
datalines;
2022-01-08 100 trn01
2022-01-09 200 trn02
2022-01-10 300 trn03
2022-01-11 400 trn04
2021-01-12 500 trn05
2022-01-13 600 trn06
2022-01-14 700 trn07
2022-01-15 800 trn08
2022-01-16 900 trn09
2022-01-17 1000 trn10

;

[Kurt_Bremser]

You run my code before your analysis, and then use

where process_date = &rundate.

to select the observations.

I used a DATA _NULL_ step to keep the code more readable; basically, you can calculate the rundate in a single %LET with a lot of %SYSFUNCs in between.