DATA Step, Macro, Functions and more

Date Macros

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Date Macros

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

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎03-08-2016 03:09 AM
Super User
Posts: 11,343

Re: Date Macros

[ Edited ]

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.

View solution in original post


All Replies
Solution
‎03-08-2016 03:09 AM
Super User
Posts: 11,343

Re: Date Macros

[ Edited ]

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.

Super User
Posts: 5,506

Re: Date Macros

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 <=

Trusted Advisor
Posts: 1,117

Re: Date Macros

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 196 views
  • 1 like
  • 4 in conversation