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;
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.
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.
@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!
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.);
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.