how to calcuate date for weekdays using toda()

[sas_student1]

Hello,

I have a record tracking patient data that is collected on a daily basis, Monday through Friday. The tracking data collects number of new patients on a daily basis. I have a date column along with number of patients. What I want to do is if the date column is todays date (so (today()) then sum the patient numbers by clients and mark them as new patients (new_pts). I want to run this every morning but grabbing the data from the night before (I have this running on a task scheduler), the issue is two fold , (1) how do I run this so it that the date column is today() -1  and (2) when I am running the data on Monday morning I need it to identify the new patients from Friday so today()-1 wont work. In the below example lets pretend that today is May 3 2021.

I have below a sample code of what I have already, but how to accomplish the two issues above?

data have;
informat client 2. date mmddyy10. no_pts 1.;
input client date no_pts;
format date mmddyy10.;
datalines;
1	04122021 1
2	04132021 1
3	04142021 2
4	04152021 2
5	04162021 0
6	04172021 1
7	04182021 1
8	04192021 1
9	05032021 1
9	05032021 1
11	05032021 1
;
run;

proc sql;
create table want as
select distinct client, 
case when date=today() then sum(no_pts) else 0 end as new_pts,
sum(no_pts) as all_pts
from have
group by client;
quit;

[mkeintz · Accepted solution]

Instead of

today()-1

you can use

intnx('weekday',today(),-1)

which will generate the same value as today()-1 for Tuesdays, Wednesdays, Thursdays, and Fridays.  But for Mondays it will generate the date of the prior Friday.

[sas_student1]

@mkeintz  Ah! thanks! simple solution!

I have another issue that I hope you are able to assist in this thread. It is kind of related.

In the same data run I am creating a proc report that I ODS export, I have a title that currently reads as follows:

title2 j=l h=11pt color=black font='Calibri'
"^{style[fontweight=bold color=black] Date Complete: %sysfunc(date(),mmddyy10.)}";

The above takes todays date and populates as date complete. How would I change the above so it would take yesterdays date and if yesterday date is a Sunday it would populate Fridays date instead? I thought adding the 'weekday' may work but it didn't. 

Thanks!

[mkeintz]

You can take the original expression

  intnx('weekday',today(),-1)

and embed each function call in %sysfunc, as in

%sysfunc(intnx(weekday,%sysfunc(date()),-1),mmddyy10.);

[sas_student1]

wow! an easy solution! I was trying to add the original function within in the %sysfunc but didn't realize that I need to also change the today() and remove the quotes.

Thank you!!!