topic Date Macros in SAS Programming

Date Macros

Adnan1 — Mon, 07 Mar 2016 15:33:10 GMT

Hi All,

I am trying to automate some code with the use of date macros but encountered some Inavlid date/time/datetime constant errors.

I have assigned the macros as below

%let StartDate ='31oct2015'd;
%put StartDate= &StartDate;
%let Enddate = intnx('month', today(),-1, 'end');
%put EndDate= &EndDate;

Where CreatedOn<='&Enddate'd and CreatedOn>='&StartDate'd

and using them in the context above.

Any help will be greatly appreciated !

Thanks

Adnan

Re: Date Macros

ballardw — Tue, 08 Mar 2016 16:04:42 GMT

Use

Where CreatedOn<=&Enddate and CreatedOn>=&StartDate

You have a few issues,

First you define Startdate as the string '31oct2015'd but then reference as '&startdate'd. Inside single quotes a macro variable does not resolve. Second, since it already has the d at the end you would be attempting to use CreatedOn = ' '31OCT2015'd'd. Recommend pick one stye and stay with it for date literals. I would recommend

%let Startdate = 31OCT2015;

and use as

CreatedOn .= "&startdate"d;

One reason for my preference is I could then use the same variable as part of a title or filename where the quotes and d would be confusing.

Second. What did you see with the %put for &enddate? You did not create a string that could be used in a date literal. And your actual code for EndDate as shown does not really seem to lend itself to a macro.

To get EndDate to contain a SAS Date value would have required multiple uses of %sysfunc. You might explain a little further why you need an EndDate macro variable and what the value should look like.

Re: Date Macros

Astounding — Mon, 07 Mar 2016 16:26:06 GMT

You have a few basic problems in your code.

First principle to digest: single quotes suppress all macro activity. So this expression does not attempt to resolve &StartDate:

CreatedOn >= '&StartDate'd

That's the easy problem to fix. Since StartDate already contains the quotes and the "d", all you need to specify is:

CreatedOn >= &StartDate

The more complex problems involve &EndDate. As you can see from your %PUT statement, the %LET statement does not try to execute DATA step functions. INTNX and TODAY() remain characters, just part of the value of &EndDate. The more advanced method to get macro language to execute DATA step functions would be to use the macro language function %SYSFUNC. I'm going to give you a different approach, though, that I think would be more appropriate based on the entire set of issues that I'm seeing here. Add a DATA step:

%let EndDate= ... same as before ...;

data _null_;

end_date = &EndDate;

call symputx('EndDate', end_date);

run;

That will replace &EndDate with the proper numeric value on SAS's date scale. You could then use:

Where &StartDate <= CreatedOn <= &EndDate;

Good luck.

where CreatedOn <=

Re: Date Macros

FreelanceReinh — Mon, 07 Mar 2016 16:42:03 GMT

Hi Adnan,

Another similar reply, in slightly different words (written in parallel to the other replies) ...

First of all, you are talking about macro variables. Macros are something else.

Macro variables store text. As you can see from the results of your %PUT statements, macro variables StartDate and EndDate contain exactly what you've entered before. Nothing has been evaluated.

The macro variable references &Enddate and &StartDate in your WHERE statement will not be resolved (i.e. replaced by the content of the respective macro variable) because they are enclosed in single quotes and therefore treated as text. But even if they were resolved (e.g. if you had used double quotes), you would receive error messages because the resulting expressions would look like "intnx('month', today(),-1, 'end')"d and "'31oct2015'd"d, both of which are invalid.

The easiest way to correct your code is to use the values of the macro variables as they are in the WHERE statement:

where CreatedOn<=&Enddate and CreatedOn>=&StartDate;

Please note that the evaluation of TODAY() will then occur when the WHERE statement is executed. If you let %SYSFUNC or a data step evaluate it already in the definition of macro variable Enddate, the results may be different if that definition and the WHERE statement are not executed on the same day.