topic Re: Date reference that always takes date of most recent Sunday? in SAS Programming

Date reference that always takes date of most recent Sunday?

illmatic — Wed, 18 Jan 2023 17:45:22 GMT

Hi all,

I have a recurring reference in my code that I'd like to automate so that I don't need to update it manually.

%LET current_week ='15Jan2023'd; /* should be last Sunday */

Here is the global reference as of this week, but it has a pattern. It's always going to be the last Sunday. Is there logic that can input this for me automatically? I was thinking a mix of today() and intx(), but I am coming up short.

Thank you for your help.

Re: Date reference that always takes date of most recent Sunday?

PaigeMiller — Wed, 18 Jan 2023 17:55:54 GMT

I find your explanation to be very unclear. Could you please clarify and give a clear example (or two)?

The date you show, 15JAN2023 is a Sunday. Are the dates always on a Sunday? Do you want the the date of the previous Sunday? That's not the same as "date of most recent Sunday". Or is it?

Can the date ever be a Wednesday and you want the most recent Sunday? In this case

data a;
    day_to_use=today(); /* I wrote this code on 18JAN2023, a Wednesday */
    previous_sunday=intnx('week',day_to_use,0,'b');
    format previous_sunday day_to_use date9.;
run;

Re: Date reference that always takes date of most recent Sunday?

illmatic — Wed, 18 Jan 2023 18:56:24 GMT

Hi Paige! My apologies, I wrote this too quickly.

So the date will always be the previous Sunday.

Example 1: Run the code today (Wednesday), result = 1/15/2023 (prior Sunday)
Example 2: Run the code on Monday (1/23/2023) next week, result = 1/22/2023 (prior Sunday)

I believe your code works in a global reference method.
%LET previous_sunday = intnx('week',today(),0,'b');

thank you very much for your help!

Re: Date reference that always takes date of most recent Sunday?

Tom — Wed, 18 Jan 2023 19:06:20 GMT

Setting a macro variable to a string that is that function call:

%LET previous_sunday = intnx('week',today(),0,'b');

Means you can use it any place where SAS would allow the same function call.

data test;
 set have;
  myvar = &previous_sunday;
  format myvar date9.;
run;

Since when the macro variable is expanded you will still have valid SAS code for SAS to run.

data test;
 set have;
  myvar = intnx('week',today(),0,'b');
  format myvar date9.;
run;

But if you try to use that in some string, like a TITLE:

TITLE "Last Sunday is &previous_sunday";

Then you also just get the same text, and not the value.

TITLE "Last Sunday is intnx('week',today(),0,'b')";

Re: Date reference that always takes date of most recent Sunday?

PaigeMiller — Wed, 18 Jan 2023 19:28:26 GMT

@illmatic wrote:
Hi Paige! My apologies, I wrote this too quickly.

So the date will always be the previous Sunday.

Example 1: Run the code today (Wednesday), result = 1/15/2023 (prior Sunday)
Example 2: Run the code on Monday (1/23/2023) next week, result = 1/22/2023 (prior Sunday)

I believe your code works in a global reference method.
%LET previous_sunday = intnx('week',today(),0,'b');

thank you very much for your help!

What should happen if the code is run on a Sunday, let's say 22JAN2023. What is the desired output? Or is it never the case that the code is run on a Sunday?

Why are you using macro variables here? What is it about the problem that macro variables are needed? How are you going to use this macro variable?

Re: Date reference that always takes date of most recent Sunday?

illmatic — Thu, 19 Jan 2023 18:35:36 GMT

Hi Paige!

The code is scheduled to run on Mondays. The intention of the reference is to automate the output instead of having to enter the date each time.
The code captures transactions and rolls it up to weekly amounts with Sundays as the reference date.

Re: Date reference that always takes date of most recent Sunday?

PaigeMiller — Thu, 19 Jan 2023 18:52:58 GMT

@illmatic wrote:

The code is scheduled to run on Mondays.

May I recommend that from now on, important pieces of information be stated in the first message in your thread.

If you are running the code on Mondays, then the Sunday of interest is much more easily computed.

sunday_before = today()-1;

The intention of the reference is to automate the output instead of having to enter the date each time.
The code captures transactions and rolls it up to weekly amounts with Sundays as the reference date.

Based on what you have said, none of this requires macro variables. The only place would be if you are going to include the date in a folder or file name or title or label or footnote, for example, then you would need a macro variable. The code I have already provided doesn't use macro variables, and yet the value of the previous Sunday is properly computed.