topic Re: first day of last month as datetime for where condition in proc sql in SAS Programming

first day of last month as datetime for where condition in proc sql

SAS_Newbie2024 — Fri, 13 Sep 2024 11:25:05 GMT

I want to make the following make the following dynamic/automated with intnx(), so I don't need to change the macrovariable manually each month I use my code. After I define it, I use it in the where condition of a SQL procedure.

%let datetime = '01aug2024 00:00:00'dt;

The following code generates the first day of the last month, however I need it exactly as stated in the static example above.

%let mydt = %sysfunc(intnx(day, %sysfunc(intnx(month, %sysfunc(date()), -1)), 0), Date9.);
%put &mydt;

When I try the following code, which should be a valid solution according to my knowledge, I get 01JAN1960:06:00:00

%let mydt = %sysfunc(intnx(day, %sysfunc(intnx(month, %sysfunc(date()), -1)), 0));
%let mydt = %sysfunc(intnx(hour, &mydt., 0));
%let mydt = %sysfunc(intnx(minute, &mydt., 0));
%let mydt = %sysfunc(intnx(second, &mydt., 0), Datetime.);
%put &mydt;

How can I solve this problem? I tried concatenation and countless other approaches that didn't work and it starts to drive me to despair. Thank you for your advice and help in advance!

Re: first day of last month as datetime for where condition in proc sql

SASJedi — Fri, 13 Sep 2024 12:21:41 GMT

I think you are overcomplicating the answer. From your example, you always want midnight on the first day of the previous month. If so, just tack on the time as a literal value. To get the resolved text surrounded by single quotes, use the built-in %tslit function. Example:

%let mydt = %tslit(%sysfunc(intnx(day, %sysfunc(intnx(month, %sysfunc(date()), -1)), 0), Date9.) 00:00:00)dt;
%put NOTE: &=mydt;

Result:

NOTE: MYDT='01AUG2024 00:00:00'dt

Re: first day of last month as datetime for where condition in proc sql

Kurt_Bremser — Fri, 13 Sep 2024 12:44:26 GMT

%let mydt = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,b));

Maxim 28: Macro Variables Need No Formats.

Re: first day of last month as datetime for where condition in proc sql

Tom — Fri, 13 Sep 2024 12:57:52 GMT

If you want to use the value in SAS code then don't bother to format it. Just let %SYSFUNC() generate the number that SAS uses to store that datetime value.

%let datetime = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1));

If you want the value to be something a human can understand then you can convert it to a datetime literal by enclosing it in quotes and appending the letters dt.

%let datetime = "%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1),datetime19.)"dt;

Example:

1    %let datetime1 = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1));
2    %let datetime2 = "%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1),datetime19.)"dt;
3    %put &=datetime1 &=datetime2;
DATETIME1=2038089600 DATETIME2=" 01AUG2024:00:00:00"dt
4    data test;
5      datetime1 = &datetime1;
6      datetime2 = &datetime2;
7      if datetime1=datetime2 then put 'SAME VALUE';
8      else put 'DIFFERENT VALUE';
9    run;

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

Note: The DATETIME format has a bug. If you attempt to use DATETIME18. you will get only 2 digits for the year.