I've a macro variable and it resolves to date value as follows.
SYMBOLGEN: Macro variable FIRSTDAYOFREPORTINGMONTH resolves to 01SEP2018
24 %put FirstDayofReportingMonth = &FirstDayofReportingMonth;
FirstDayofReportingMonth = 01SEP2018
25 %put LastDayofReportingMonth = &LastDayofReportingMonth;
SYMBOLGEN: Macro variable LASTDAYOFREPORTINGMONTH resolves to 30SEP2018
LastDayofReportingMonth = 30SEP2018
Now I need to extract the data based on date value and I should not hard code the date value. Therefore I tried to to filter the data with the use of macros, but I'm getting the error as mentioned below. Appreciate if someone of you tell me to know to extract the data with the help of date macro.
/*ERROR-1*/
68 /* where KNDTOP between '01sep2018'd and '30sep2018'd*/
69 where KNDTOP between &FirstDayofReportingMonth. and &LastDayofReportingMonth.
SYMBOLGEN: Macro variable FIRSTDAYOFREPORTINGMONTH resolves to 01SEP2018
SYMBOLGEN: Macro variable LASTDAYOFREPORTINGMONTH resolves to 30SEP2018
NOTE 138-205: Line generated by the macro variable "FIRSTDAYOFREPORTINGMONTH".
69 01SEP2018
_______
22
ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, AND, ||.
/*ERROR-2*/
68 /* where KNDTOP between '01sep2018'd and '30sep2018'd*/
69 where KNDTOP between '&FirstDayofReportingMonth.'d and '&LastDayofReportingMonth.'d
ERROR: Invalid date/time/datetime constant '&FirstDayofReportingMonth.'d.
ERROR: Invalid date/time/datetime constant '&LastDayofReportingMonth.'d.
You need double quotes!
where KNDTOP between "&FirstDayofReportingMonth."d and "&LastDayofReportingMonth."d
That double quotes are necessary should be clear to you since 2015-12-16: https://communities.sas.com/t5/SAS-Programming/Understanding-of-macro-quotes-and-ampersand/td-p/2394...
If my macro variable resolves to 20180630, I'm unable to resolve this with double quotes. See the error below. May I know what I'm missing here?
7262 and F_CLM_CLAIM_EOM.REPORTING_DATE_SK = "&LASTDAYOFREPORTINGMONTH."d
SYMBOLGEN: Macro variable LASTDAYOFREPORTINGMONTH resolves to 20180630
ERROR: Invalid date/time/datetime constant "20180630"d.
It is invalid to create a date value of "20180630"d.
It is valid to create a date value of "30JUN2018"d.
Thus, you should be creating macro variables that have values formatted as 30JUN2018, or better yet (this is the preferred solution) don't use formatted dates here, just use that actual date value, unformatted, and you won't need double quotes and you won't need the letter d after the final double quote. Formats are so humans can understand the date value; SAS doesn't need this, and so it is a waste of time to format the values so humans can read it and then "unformat it" by putting the date in double quotes with the letter d after it.
Do a google search for "sas date literals", and study the results.
You've been told about this here on the communities repeatedly, eg in https://communities.sas.com/t5/SAS-Programming/Date-variable-calculation/td-p/425702 and https://communities.sas.com/t5/SAS-Programming/Char-to-num-for-DATE/td-p/497722
If your memory is THAT leaky, you should consider other endeavors than working with SAS, as it requires one to keep at least the important things that were learned once.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.