And now applying @Tom's logic to your code things could look like:
%let old = %sysfunc(intnx(weekday17w,%sysfunc(today()),-2));
%let new = %sysfunc(intnx(weekday2w,%sysfunc(today()),-1)) ;
proc sql;
create table daily_t as
select
date,
case
when date = &old then 'previous'
else 'current'
end
as period,
accno,
branchnumber,
accbal
from account_daily_table
where date in (&old, &new);
quit;
You can of course also use the intnx() functions directly within your SQL in place of the macro variables.
Hi Patrick,
I am using the below as you've stated and it worked perfectly when I ran the code yesterday (Monday) where it gave me the old date = Thursday and new date = Saturday however it is not working when I ran today (Tuesday). It gave me old date = Friday and new date = Sunday when it should be old date = Saturday and new date = Mon
%let old = %sysfunc(intnx(weekday17w,%sysfunc(today()),-2));
%let new = %sysfunc(intnx(weekday2w,%sysfunc(today()),-1)) ;
proc sql;
create table daily_t as
select
date,
case
when date = &old then 'previous'
else 'current' end as period,
accno,
branchnumber,
accbal
from account_daily_table where date in (&old, &new);
quit;
Appreciate your help.
Thanks kindly,
Mags
I've missed that the example @Tom posted used the first (and wrong) logic to determine the old and new dates.
Still following what @Tom posted but amending to the final desired result below should do the job.
The first data step is just re-implementing Tom's sample code with the amended logic, the second data step is how you could implement this.
I've chosen this time a data NULL step to create the macro variables to avoid this sometimes hard-to-read spaghetti code when using %sysfunc().
data test ;
do today = '16APR2018'd to '20APR2018'd ;
new = intnx('weekday1w',today,-1) ;
old = intnx('weekday1w',intnx('weekday17w',today,-1),-1) ;
day1 = put(today,downame.-l);
day2 = put(old,downame.-l);
day3 = put(new,downame.-l);
output;
format today old new yymmdd10. ;
end;
run;
proc print; run;
data _null_;
format
executionDate previousExecutiondate
startDate endDate WEEKDATX.;
/* executionDate='17APR2018'd;*/
executionDate =today();
previousExecutiondate =intnx('weekday17w',executionDate,-1);
startDate =intnx('weekday1w',previousExecutiondate,-1);
endDate =intnx('weekday1w',executionDate,-1);
put _all_;
call symputx('old',put(startDate,10.));
call symputx('new',put(endDate,10.));
stop;
run;
Hi Patrick,
Thanks for your response. How do I use your logic for the following? I don't have indepth knowledge in SAS but aiming to get to know SAS a lot better. Your help will solve a 4 week project that I've been working on.
Your logic:
data test;
format
executionDate previousExecutiondate
startDate endDate WEEKDATX.;
/* executionDate='17APR2018'd;*/
executionDate =today();
previousExecutiondate =intnx('weekday17w',executionDate,-1);
startDate =intnx('weekday1w',previousExecutiondate,-1);
endDate =intnx('weekday1w',executionDate,-1);
put _all_;
call symputx('old',put(startDate,10.));
call symputx('new',put(endDate,10.));
stop;
run;
My code:
* Date Comparisons;
%let old = '17Apr2018'd; ;*old date;
%let new = '18Apr2018'd; ;*new date
%_eg_conditional_dropds(WORK.DAILY_T);
PROC SQL;
CREATE TABLE WORK.DAILY_T AS SELECT
Date,
AccNo,
BranchNumber,
AccBal
FROM ACCOUNT_DAILY_TABLE
WHERE Date IN (&old, &new);
RUN;
Thanks kindly,
Mags
Hi @Timbim (Mags),
You simply need some sort of code which populates two SAS macro variables with the correct SAS data values. There are very often different coding options available. The code I've posted (data _null_ step) does nothing else than creating and populating the SAS macro variables &old and &new and though the code would look like:
data _null_;
format
executionDate previousExecutiondate
startDate endDate WEEKDATX.;
executionDate =today();
previousExecutiondate =intnx('weekday17w',executionDate,-1);
startDate =intnx('weekday1w',previousExecutiondate,-1);
endDate =intnx('weekday1w',executionDate,-1);
/* put _all_;*/
call symputx('old',put(startDate,10.));
call symputx('new',put(endDate,10.));
stop;
run;
proc sql;
create table work.daily_t as select
date,
accno,
branchnumber,
accbal
from account_daily_table
where date in (&old, &new);
run;
Another coding option is to use %sysfunc() which allows for usage of SAS Base functions on SAS Macro level. I like this coding option BUT if one needs more than one SAS function to get to the desired result then the code becomes quickly very hard to understand and maintain. That's why I don't recommend to use below alternative here. It's just for demonstration.
%let old=%sysfunc(intnx(weekday1w,%sysfunc(intnx(weekday17w,%sysfunc(today()),-1)),-1));
%let new=%sysfunc(intnx(weekday1w,%sysfunc(today()),-1));
proc sql;
create table work.daily_t as select
date,
accno,
branchnumber,
accbal
from account_daily_table
where date in (&old, &new);
run;
Hi Patrick,
Thank you so so much. I've just tested your first code and it is working now for execution day Wednesday. I'll test for execution Day Monday and Tuesday and post a response.
data _null_;
format
executionDate previousExecutiondate
startDate endDate WEEKDATX.;
executionDate =today();
previousExecutiondate =intnx('weekday17w',executionDate,-1);
startDate =intnx('weekday1w',previousExecutiondate,-1);
endDate =intnx('weekday1w',executionDate,-1);
call symputx('old',put(startDate,10.));
call symputx('new',put(endDate,10.));
stop;
run;
Thank you for being very helpful.
Kind regards,
Mags
Hi Patrick,
You are amazing and thank you. Your code worked perfectly and I was able to complete my project successfully.
Thanks heaps!
Thank you.
The SAS calendar functions like intnx() and intck() are indeed very powerful and once you get the hang of it allow very often for simple coding solutions to "shifting dates" problems which feel at first hard to solve.
@Timbim wrote:
Hi Team,
I am working on automating some of the manual steps in a new team and in need of your SAS knowledge.
There is this particular date logic within SAS that my new team keeps changing every day.
Whilst I know how to determine the previous day and today's date, there is this other additional weekly change that I don't know how to do, hence I am here.
My new team have to change the reporting date as per follows:
1. Every Monday
'old date' must be Thursday of last week and 'new date' must be Saturday of last week
2. Every Tuesday
'old date' must be Friday of last week and 'new date' must be Sunday of last week.
3. Every other day
'old date' = previous day
'new date' = today () minus 1
would there be a function that could do all 1, 2 & 3 ?
Truly appreciate your help,
Mags
This code delivers exactly what you want:
data _null_;
format date old_date new_date weekdate30.;
do date = today() - 7 to today();
if weekday(date) in (2,3)
then do;
old_date = date - 4;
new_date = date - 2;
end;
else do;
old_date = date - 1;
new_date = today() - 1;
end;
put date=;
put old_date=;
put new_date=;
end;
run;
Log:
27 data _null_; 28 format date old_date new_date weekdate30.; 29 do date = today() - 7 to today(); 30 if weekday(date) in (2,3) 31 then do; 32 old_date = date - 4; 33 new_date = date - 2; 34 end; 35 else do; 36 old_date = date - 1; 37 new_date = today() - 1; 38 end; 39 put date=; 40 put old_date=; 41 put new_date=; 42 end; 43 run; date=Tuesday, April 17, 2018 old_date=Friday, April 13, 2018 new_date=Sunday, April 15, 2018 date=Wednesday, April 18, 2018 old_date=Tuesday, April 17, 2018 new_date=Monday, April 23, 2018 date=Thursday, April 19, 2018 old_date=Wednesday, April 18, 2018 new_date=Monday, April 23, 2018 date=Friday, April 20, 2018 old_date=Thursday, April 19, 2018 new_date=Monday, April 23, 2018 date=Saturday, April 21, 2018 old_date=Friday, April 20, 2018 new_date=Monday, April 23, 2018 date=Sunday, April 22, 2018 old_date=Saturday, April 21, 2018 new_date=Monday, April 23, 2018 date=Monday, April 23, 2018 old_date=Thursday, April 19, 2018 new_date=Saturday, April 21, 2018 date=Tuesday, April 24, 2018 old_date=Friday, April 20, 2018 new_date=Sunday, April 22, 2018 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds
although it esacpes me why you want today()-1 as new_date for all weekdays except Monday&Tuesday. Shouldn't that also be derived from date?
Hi Kurt,
Every Tuesday statement made in the first posting is incorrect. It should be as per below:
Execution Date | start date | end date |
Monday | Thursday | Saturday |
Tuesday | Saturday | Monday |
Wednesday | Monday | Tuesday |
Thursday | Tuesday | Wednesday |
Friday | Wednesday | Thursday |
Below is what I need when I run the code on specific days :
Every Mondays, I want it to return me Thursday's and Saturday's records
Every Tuesdays, I want it to return me Saturday's and Monday's records
Every Wednesday, I want it to return Monday's and Tuesday's records
Every Thursday, I want it to return Tuesday's and Wednesday's records
Every Friday, I want it to return Wednesday's and Thursday's records
Your help is highly appreciated.
Thanks,
Mags
Then this logic does it:
data test (keep=date old_date new_date);
format date old_date new_date weekdate30.;
monday = today() - weekday(today()) + 2;
friday = monday + 4;
do date = monday to friday;
if weekday(date) = 2
then do;
old_date = date - 4;
new_date = date - 2;
end;
else if weekday(date) = 3
then do;
old_date = date - 3;
new_date = date - 1;
end;
else do;
old_date = date - 2;
new_date = date - 1;
end;
output;
end;
run;
proc print data=test noobs;
run;
Result:
date old_date new_date
Monday, April 23, 2018 Thursday, April 19, 2018 Saturday, April 21, 2018
Tuesday, April 24, 2018 Saturday, April 21, 2018 Monday, April 23, 2018
Wednesday, April 25, 2018 Monday, April 23, 2018 Tuesday, April 24, 2018
Thursday, April 26, 2018 Tuesday, April 24, 2018 Wednesday, April 25, 2018
Friday, April 27, 2018 Wednesday, April 25, 2018 Thursday, April 26, 2018
This just to show that the logic in the loop works.
For creation of the two macro variables, remove the do loop, set date from today(), and use call symputx() at the end.
Hi Kurt,
Perhaps my question should be, how do I reference the table created from your step? My knowledge of sas is not indepth but I want to get to get to know and use sas a lot better.
Thanks kindly for your help.
Mags
I created table test solely for the purpose of showing that the logic works, so I could use proc print to display the values in a nice fashion.
Instead of creating a table in a loop, set variable date to a value of your chosing (eg today()), and use call symputx (see documentation) to create macro variables for further use.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.