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
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;
You want to use the WEEKDAY function to determine if the day of the week is Monday, Tuesday, etc.
From there, a series of IF statements ought to get the proper 'old date' and 'new date'.
Since #1 and #2 have the same math, the statements are quite simple:
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;
if date = today() and weekday(date) neither a Monday nor a Tuesday, old_date and new_date would be identical.
Thanks Kurt. How can I write that in a Macro?
Kind regards,
Maggie
@Timbim wrote:
Thanks Kurt. How can I write that in a Macro?
Kind regards,
Maggie
Very simple: you don't. This is data handling, for which the data step is the tool of choice. If you need certain values later on in your program, use call symput() or call symputx() to store them in macro variables.
Please post some example data (see my footnote for this), and a matching example for the expected output (the expected output doesn't need to be in a data step).
Hi Kurt,
Below is the current example and please note every Mondays, the 'old date' must be Thursdays and the 'new date' must be Saturdays. Every Tuesdays, the 'old date' must be Saturdays and 'new date' must be Mondays. Every other day from Wed-Friday 'old date' is today() minus 2 and 'new date' is today() minus 1.
* 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;
I can easily use the intnx function to do Wed-Fri like below but having issue trying to figure out a way to do Mon and Tues.
For example:
Proc Sql;
Create Table DAILY_T as
Select Date,
(case
when Date = today()2 then 'Previous' else 'Current'
end) as Period,
AccNo,
BranchNumber,
AccBal
From ACCOUNT_DAILY_TABLE
where Date >=intnx ('day',today(),-2);
Quit;
Can you please help me with a function or data step to do all in once:
if Monday, then 'old date' is Thur and 'new date' is Saturday
if Tuesday, the 'old date' is Saturday and 'new date' is Monday
else 'old date' is today() minus 2 and 'new date' is today() minus 1
----OUTPUT-----
The output has to have 2 records of the same account - 1 record for 'old date' and 1 record for 'new date'
Highly value your help.
Thanks kindly,
Mags
I see code, but no example data to apply it to. I need example data to play around with, posted in a data step (see second link in my footnote).
Hi Kurt,
Below are dummy figures in the exact same format like the real data.
Date | AccNo | BranchNumber | AccBal |
12-Apr-18 | 1234567 | 123 | 2345.87 |
13-Apr-18 | 1234567 | 123 | 2345.87 |
14-Apr-18 | 1234567 | 123 | 2345.87 |
15-Apr-18 | 1234567 | 123 | 2345.87 |
16-Apr-18 | 1234567 | 123 | 2345.87 |
17-Apr-18 | 1234567 | 123 | 2345.87 |
18-Apr-18 | 1234567 | 123 | 2345.87 |
12-Apr-18 | 3456789 | 456 | 5678.47 |
13-Apr-18 | 3456789 | 456 | 5678.47 |
14-Apr-18 | 3456789 | 456 | 5678.47 |
15-Apr-18 | 3456789 | 456 | 5678.47 |
16-Apr-18 | 3456789 | 456 | 5678.47 |
17-Apr-18 | 3456789 | 456 | 5678.47 |
18-Apr-18 | 3456789 | 456 | 5678.47 |
Thanks kindly,
Mags
The required rule for date selections is not really clear to me. Can you please confirm or amend below assumption?
Execution Day | Start Day | End Day |
Monday | Thursday | Saturday |
Tuesday | Friday | Sunday |
Wednesday | Monday | Tuesday |
Thursday | Tuesday | Wednesday |
Friday | Wednesday | Thursday |
From what you describe I assume there is no execution on week ends.
You've also posted the following code:
when Date = today() - 2 then 'Previous' else 'Current
How does this look on a Monday (execution day). Are you also selecting Friday? And if so is Friday categorized as "Previous" or as "Current"?
I believe once we fully understand the selection logic and things really depend only on the execution date - today() - then some improved usage of the intnx() function will solve the problem.
"If today is Monday, give me me Thursday and Saturday’s data"
So also on Monday and Tuesday we're also selecting only 2 days? So on a Monday we're only selecting Thursday and Saturday and we skip Friday? Please confirm!
...and: I've amended my table based on how I understand you. What happens to Friday?
Execution Date | start date | end date |
Monday | Thursday | Saturday |
Tuesday | Saturday | Monday |
Wednesday | Monday | Tuesday |
Thursday | Tuesday | Wednesday |
Friday | Wednesday | Thursday |
Can you please just post above table with the right days?
You can do most of this with INTNX() function using the WEEKDAY interval and specifying the proper week-end days. You will need to use different "week-end" days for the OLD and NEW date calculations.
old = intnx('weekday17w',today,-2) ;
new = intnx('weekday2w',today,-1) ;
For the OLD date you are just treating Sunday (1) and Saturday (7) as the week-end days. So Monday - 2 > Thursday.
For the NEW data you only need to treat Monday (2) as the week-end day.
Example:
data test ;
do today = '16APR2018'd to '20APR2018'd ;
old = intnx('weekday17w',today,-2) ;
new = intnx('weekday2w',today,-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;
Obs today old new day1 day2 day3 1 2018-04-16 2018-04-12 2018-04-14 Monday Thursday Saturday 2 2018-04-17 2018-04-13 2018-04-15 Tuesday Friday Sunday 3 2018-04-18 2018-04-16 2018-04-17 Wednesday Monday Tuesday 4 2018-04-19 2018-04-17 2018-04-18 Thursday Tuesday Wednesday 5 2018-04-20 2018-04-18 2018-04-19 Friday Wednesday Thursday
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.