topic Re: Comparing against a DATETIME19 variable and macros in New SAS User

Comparing against a DATETIME19 variable and macros

newbatprocsql1 — Fri, 06 Sep 2024 00:50:17 GMT

Hi,

I have a table that has a variable ClosedDt of types Num, Len 8, Format DATETIME19. and Informat DATETIME19.

I have a proc sql statement that would like to filter this table every month and this involves comparing against this variable. Could I please get some help on how best to write a simple macro variable to do this?
Please see the below for what I mean

%let curr_dt = 2407 /*this is in the form YYMM for month end*/


proc sql;
 create table output as
select a.CaseID as count_caseID
from source_data_&curr_dt. as a
where ClosedDt < "01AUG2024"d and ClosedDT >= "01JUL2024"d
;quit;

1. I know that the WHERE statement will produce zero observations. How can I rewrite it?

2. Could someone please show me how I can use the curr_dt macro variable to automate the WHERE statement (i.e. change the ClosedDt < "01AUG2024"d into something like ClosedDt < &dt_start. where dt_start will resolve to "01AUG2024", based on my curr_dt variable?

Re: Comparing against a DATETIME19 variable and macros

Tom — Fri, 06 Sep 2024 03:17:03 GMT

Why would you compare DATETIME values (number of seconds) to DATE values (number of days)?

So you are assuming that all of the dates are going to be in the 21st century?

You can convert your four digit strings, like 2407, into a DATE value by using the INPUT() function (or in macro code using the %SYSFUNC() function to call the INPUTN() function) by prefixing it with 20 to set the century and suffixing it with 01 to set the day of the month.

%let curr_dt = 2407 ;
%let today = %sysfunc(inputn(20&curr_dt.01,yymmdd8.));

You can convert a DATE value into a DATETIME value by using the DHMS() function.

%let now=%sysfunc(dhms(&today,0,0,0));

Or perhaps by just multiplying by the number of seconds in a day.

%let now=%sysevalf(&today*'24:00:00't);

You can use INTNX() to find when the next month starts.

where ClosedDT >= &now
  and ClosedDt < %sysfunc(intnx(dtmonth,&now,1))