How to subtract months to a date in DATETIME25.6 format (e.g., 30JAN2025:00:00:00.000000)

[donspaul]

Hello,

I need to subtract a certain number of months to dates that are in DATETIME25.6 format.  For example, if the original date is 31DEC2024:00:00:00.000000 and I would like to subtract 1 month, I would like the resulting date to be 30NOV2024:00:00:00.000000.  Both the original date and the resulting date should be in DATETIME25.6 format.  I tried using the following in a query and the RESULTING_DATE is showing up as a missing value.

INTNX('MONTHS',ORIGINAL_DATE,-11,'E') AS RESULTING_DATE FORMAT=DATETIME25.6

Any help would be greatly appreciated.

Thank you,

Don

[PaigeMiller · Accepted solution]

INTNX('DTMONTH',ORIGINAL_DATE,-11,'E') AS RESULTING_DATE FORMAT=DATETIME25.6

When you are working with Date/Time values, you must use DTMONTH (beginning with DT to indicate that you want date/time calculations). Also note it is DTMONTH and not plural DTMONTHS

[donspaul]

Thank you.  That worked, but I have another issue related to my original post.  

I need to pass the resulting date to a macro variable and I did that using the following code:

PROC SQL;
     SELECT RESULTING_DATE
     INTO: RESULTING_DATE
     FROM TIME_DATA;
QUIT;

However, when I did that, it resolves to 30NOV2024.  Could you please let me know how I could reformat the macro variable %RESULTING_DATE to be in DATETIME25.6 format.

Thanks again for your assistance.

Don

[PaigeMiller]

In most cases, macro variables should be unformatted, not formatted.  Maxim 28

What do you plan to do with this macro variable?

[Tom]

That would only happen if you had attached the DTDATE9. format to the variable.

1    data test;
2      dt=datetime();
3      date=dt;
4      format dt datetime19. date dtdate9.;
5    run;

NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


6
7    proc sql noprint;
8    select dt,date
9      into :dt,:date
10     from test;
11   ;
12   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


13   %put &=dt &=date ;
DT= 10FEB2025:15:06:36 DATE=10FEB2025

What do you plan to do with the macro variable?

If you plan to compare it to other SAS variables with datetime values then take the unformatted value.  (That is the raw number of seconds).

If you plan to use it in a title or other text string then apply the format you want in the SELECT statement that makes the macro variable.

14
15   proc sql noprint;
16   select dt format=32.
17        , dt format=dtdate9.
18        , dt format=datetime19.
19     into :raw_dt trimmed
20        , :date trimmed
21        , :dt trimmed
22     from test
23   ;
24   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


25   %put &=raw_dt &=date &=dt ;
RAW_DT=2054819196 DATE=10FEB2025 DT=10FEB2025:15:06:36

[donspaul]

Tom,

You are right.  The date resolved to the correct format in the macro variable.  I should have checked the Macro Variable Viewer before posting in the first place.  My bad.  I am trying to set up an automation where my code automatically pulls rolling 12-month of data based on the date field.  I have approached this as follows:

1. My code checks the max date that is in the data table which represents the most recent month of data (i.e., the "END_DATE" for my purposes)

2. Using the DTTIME option in INTNX I calculate the 12-month PRIOR date, (i.e., the "BEGIN_DATE" for my purposes)

3. I create %END_DATE and %BEGIN_DATE macro variables based on steps #1 and #2 above.  This way when I run this code next month, it will pull the appropriate rolling 12-month window without manually having to specify it each time.

4. I pass the macro variables in the WHERE statement in PROC SQL to only return data rows that are between %BEGIN_DATE and %END_DATE

This is the syntax for the WHERE clause I am using:

where (&STRT_DATE <= a.veh_first_quot_tstmp_local and a.veh_first_quot_tstmp_local <= &END_DATE)

However, when I pass the macro variable in step #4, I am getting the following error in my query which I am not able to decipher.  The date format in the source table as well as the macro variables are both DATE25.6.  In my data,  %END_DATE is 30JAN2025:00:00:00.000000 and %BEGIN_DATE is 01FEB2024:00:00:00.000000

ERROR: CLI prepare error: [Redshift][ODBC Driver][Server]42601:ERROR: syntax error at or near "FEB2024" in context "where
(01FEB2024", at line 1 LINE 1, POSITION 50: ..._id and a.hhld_exp_cnt = b.hhld_exp_cnt) where (01FEB2024:00...

[PaigeMiller]

where (&STRT_DATE <= a.veh_first_quot_tstmp_local and a.veh_first_quot_tstmp_local <= &END_DATE)

Arithmetic operations and logical operations work properly with unformatted macro variables. 

To get unformatted macro variables, use 

select variablename format=best32. into :strt_date from have

(Best32. produces the integer that represents the date/time value, essentially this is the unformatted value. If you don't like all the spaces before this integer in the macro variable use select variablename format=best32. into :strt_date trimmed from have)

[donspaul]

THANK YOU!!!  This finally worked (and yes, I double, triple checked this time around:-) ).  Using unformatted macro vars was the key.  It worked fine without trimming the spaces.  

[donspaul]

I figured out the issue, I forgot to put a period when calling the macro variable in the WHERE clause, I modified the syntax as follows and it works exactly as intended.

where (&STRT_DATE. <= a.veh_first_quot_tstmp_local and a.veh_first_quot_tstmp_local <= &END_DATE.)

Thanks again for all the thought partnership on this.

Don

[PaigeMiller]

---

@donspaul wrote:

I figured out the issue, I forgot to put a period when calling the macro variable in the WHERE clause, I modified the syntax as follows and it works exactly as intended.

where (&STRT_DATE. <= a.veh_first_quot_tstmp_local and a.veh_first_quot_tstmp_local <= &END_DATE.)

---

This would not cause the error messages you saw. More than likely, the error messages were because you used formatted macro variables instead of unformatted macro variables.

[donspaul]

You were right. This was not the issue. The code was run with another macro variable that used to work properly. I am going to try the unformatted version per your instructions and see how that goes. Thanks again for your help.

[Tom]

You asked SAS to subtract around 11 times 30 from the value since there are about 30 days in month.

But since your variable has DATETIME values it is stored in SECONDS, not DAYS.  So even if that worked it would only change the value by about 5 and half minutes.

Use the DTMONTH interval instead.