BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Adnan1
Calcite | Level 5

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

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
ballardw
Super User

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.

Astounding
PROC Star

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

FreelanceReinh
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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