topic Re: Date Macro or Function in SAS Programming

Date Macro or Function

Timbim — Mon, 09 Apr 2018 11:37:14 GMT

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

Re: Date Macro or Function

PaigeMiller — Mon, 09 Apr 2018 11:49:20 GMT

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'.

Re: Date Macro or Function

Kurt_Bremser — Mon, 09 Apr 2018 11:58:44 GMT

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.

Re: Date Macro or Function

Timbim — Wed, 11 Apr 2018 04:29:34 GMT

Thanks Kurt. How can I write that in a Macro?

Kind regards,

Maggie

Re: Date Macro or Function

Kurt_Bremser — Wed, 11 Apr 2018 05:46:37 GMT

@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.

Re: Date Macro or Function

Timbim — Mon, 16 Apr 2018 15:45:17 GMT

Hi Kurt, I tried your statements but it is not quiet giving me the output I am looking for.

It is an account level data and must only spit out 2 records of data for 1 account that must contain the ‘old date ‘ date and ‘new date’ data only
If the weekday is Monday , give me data for Thursday and Saturday, if the week day is Tuesday give me data for Friday and Sunday else give me yesterday and the previous day data.

Your help is highly appreciated.
Kind regards,
Mags

Re: Date Macro or Function

Kurt_Bremser — Mon, 16 Apr 2018 15:49:23 GMT

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).

Re: Date Macro or Function

Timbim — Thu, 19 Apr 2018 12:58:11 GMT

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

Re: Date Macro or Function

Kurt_Bremser — Thu, 19 Apr 2018 13:19:29 GMT

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).

Re: Date Macro or Function

Timbim — Thu, 19 Apr 2018 14:00:36 GMT

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

Re: Date Macro or Function

Kurt_Bremser — Fri, 20 Apr 2018 08:56:45 GMT

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Date Macro or Function

Patrick — Fri, 20 Apr 2018 10:10:18 GMT

@Timbim

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.

Re: Date Macro or Function

Timbim — Fri, 20 Apr 2018 10:22:38 GMT

Hi Patrick,

The assumption you made on the table are correct expect for Tuesday:

Execution Day = Tuesday
Start Day = Saturday
Finish Day = Monday

For Monday:

Execution Day = Monday
Start Day = Thursday
Finish Day = Saturday

The code I posted can only give me current and previous day data of other days except for Monday and Tuesday. I need help with a logic for Monday and Tuesday.

To put in words:
If today is Monday, give me me Thursday and Saturday’s data
If today is Tuesday, give me Saturday and Monday’s days
else last 2 days data. I hope this makes sense.

Highly value your help.

Kind regards,
Mags

If Tu

Re: Date Macro or Function

Patrick — Fri, 20 Apr 2018 10:35:01 GMT

@Timbim

"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?

Re: Date Macro or Function

Tom — Fri, 20 Apr 2018 12:59:31 GMT

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

Re: Date Macro or Function

Timbim — Fri, 20 Apr 2018 14:15:34 GMT

@ Patrick you’ve understood correctly. The execution, start and end dates are correct on your table. Yes, we skip Friday. The tables have account information and we get a snapshot of Saturday’s end of day balance which already includes Friday.

Thanks kindly,
Mags

Re: Date Macro or Function

Patrick — Fri, 20 Apr 2018 23:15:23 GMT

@Timbim

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.

Re: Date Macro or Function

Timbim — Tue, 24 Apr 2018 05:58:34 GMT

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

Re: Date Macro or Function

Kurt_Bremser — Tue, 24 Apr 2018 06:27:04 GMT

@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?

Re: Date Macro or Function

Timbim — Tue, 24 Apr 2018 06:47:03 GMT

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