Using %macro as a filter

[BarisGir]

hi guys,

i'm using this code to create last 3 end of months as a filter in SAS EG (program) 

%macro last_three_months_end_dates;
%let today = %sysfunc(today());
%let end_date1 = %sysfunc(intnx(month, &today, -1, E));
%let end_date2 = %sysfunc(intnx(month, &today, -2, E));
%let end_date3 = %sysfunc(intnx(month, &today, -3, E));

%let end_date1 = %sysfunc(putn(&end_date1, datetime20.));
%let end_date2 = %sysfunc(putn(&end_date2, datetime20.));
%let end_date3 = %sysfunc(putn(&end_date3, datetime20.));

%global date_list;
%let date_list = "&end_date1"dt, "&end_date2"dt, "&end_date3"dt;

%mend last_three_months_end_dates;

%last_three_months_end_dates;

and use  em in filter like this 

WHERE t1.TARIH_ID IN (&date_list)

But this creates really different date format as i mention below and i cannot solve this issue 

"WHERE ( ("TARIH_ID" IN (TO_DATE('
01JAN1960:06:32:37','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American') ,TO_DATE('
01JAN1960:06:33:08','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American') ,TO_DATE('
01JAN1960:06:33:39','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American') ) ) )"

i'm about to lose my mind

[PaigeMiller]

%let end_date1 = %sysfunc(intnx(month, &today, -1, E));
%let end_date2 = %sysfunc(intnx(month, &today, -2, E));
%let end_date3 = %sysfunc(intnx(month, &today, -3, E));

%let end_date1 = %sysfunc(putn(&end_date1, datetime20.));
%let end_date2 = %sysfunc(putn(&end_date2, datetime20.));
%let end_date3 = %sysfunc(putn(&end_date3, datetime20.));

Since originally, these three macro variables contain DATE values, you cannot use PUTN to turn them into datetime variables. PUTN does not have access to the math that will turn date values into datetime values. You have to change them to datetime values first, one way is with the DHMS function. 

This should work:

%let end_date1 = %sysfunc(dhms(&end_date1,0,0,0)); /* Now &end_date1 is a datetime value */
%put %sysfunc(putn(&end_date1,datetime20.)); /* Write value to log */

You might want to do yourself a favor and give meaningful names to your macro variables, so how about calling them &end_date_time1 etc.:

%let end_date_time1 = %sysfunc(dhms(&end_date1,0,0,0)); /* Now &end_date1 is a datetime value */
%put %sysfunc(putn(&end_date_time1,datetime20.)); /* Write value to log */

[ballardw]

You have created dates that are numerically the number of days since 1JAN1960. Then you %putn them using a DATETIME format, which wants a datetime values, which is number of seconds from midnight 1Jan1960

So the question is if you want and actual datetime value or a datevalue.

If you want datetime values  you might want to try

%let today = %sysfunc(dhms(%sysfunc(today()),0,0,0));
%let end_date1 = %sysfunc(intnx(dtmonth, &today, -1, E));

The DHMS function creates a datetime value from a date, hour, minute and second value.

The DTMONTH interval tells INTNX you are using datetime values instead.

The end_date value will have a time component of 23:59:59 of the last day of the month requested.

Better would be to show us code that ran properly without any macro variables.

[Tom]

Why did you try to apply a DATETIME format to DATE values?

The DATE of 30JUN2024 is the number 23,447.  Which when printed using the DATETIME format is some time early in the morning of 01JAN1960.

374  data test;
375    number = "01JAN1960:06:32:37"dt ;
376    datetime = put(number,datetime19.-L);
377    date=put(number,date9.);
378    put number=:comma. datetime= date=;
379  run;

number=23,557 datetime=01JAN1960:06:32:37 date=30JUN2024
NOTE: The data set WORK.TEST has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

Do you even need the values to be formatted at all?  (You might since you seem to be trying to use them to subset data from some foreign database.)

Since you are trying to match the datetime values exactly do you even know what time of day you should be using?  I suspect that you should be using midnight. Especially since you seem to think these datetime values actually represent dates and not specific times of day on those dates.  Check the values in your dataset and see if any of them include a time of day part or if they all have been recorded as midnight on the date in question.

Is there any reason to have the macro generate a macro variable instead of just returning the values?

So perhaps you want something like this instead:

%macro last_three_months_end_datetimes;
%local today i ;
%let today = %sysfunc(today());
%do i=-3 %to -1 ;
 "%sysfunc(intnx(month,&today,&i,e),date9.):00:00:00"dt 
%end;
%mend last_three_months_end_datetimes;

Which you could then use to generate your WHERE clause.

Let's try it:

424  %put where datetimevar in (%last_three_months_end_datetimes);
where datetimevar in ("30JUN2024:00:00:00"dt  "31JUL2024:00:00:00"dt  "31AUG2024:00:00:00"dt)

Notice that I just let it generate spaces between the values. This will make the value much easier to use in macro code since the commas will accidentally get treated as delimiters.  SAS does not care if the you use spaces or commas (or some combination of both) in the item list for the IN operator.

[Quentin]

I'm confused.  When I run your macro, like:

%put WHERE t1.TARIH_ID IN (&date_list)  ;

I get:

WHERE t1.TARIH_ID IN ("01JAN1960:06:33:39"dt, "01JAN1960:06:33:08"dt, "01JAN1960:06:32:37"dt)

I understand that's not the value you want.  But in your question, it looks like you're getting a TO_DATE, which is apparently a PROC DS2 function.

Is the TO_DATE function somewhere in your code? Or it being added by the EG filter?  (I don't know how EG filters work).

If your goal is got get values like:

31AUG2024:23:59:59
31JUL2024:23:59:59
30JUN2024:23:59:59

You can get that by using the datetime() function to get the current date-time, instead of the today() function which gives you the current date.  Also in the INTNX function use DTMONTH as the interval instead of MONTH.  You can do it all in one statement in the macro language, like:

%put %sysfunc(intnx(dtmonth, %sysfunc(datetime()), -1, E),datetime20) ;
%put %sysfunc(intnx(dtmonth, %sysfunc(datetime()), -2, E),datetime20) ;
%put %sysfunc(intnx(dtmonth, %sysfunc(datetime()), -3, E),datetime20) ;