topic Re: Extract date macro variable in SAS Programming

Extract date macro variable

Babloo — Mon, 22 Oct 2018 06:50:03 GMT

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.

Re: Extract date macro variable

andreas_lds — Mon, 22 Oct 2018 07:18:03 GMT

You need double quotes!

where KNDTOP between "&FirstDayofReportingMonth."d and "&LastDayofReportingMonth."d

Re: Extract date macro variable

Kurt_Bremser — Mon, 22 Oct 2018 07:38:55 GMT

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/239482

Re: Extract date macro variable

Babloo — Mon, 22 Oct 2018 13:28:17 GMT

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.

Re: Extract date macro variable

PaigeMiller — Mon, 22 Oct 2018 13:45:05 GMT

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.

Re: Extract date macro variable

Kurt_Bremser — Mon, 22 Oct 2018 13:57:04 GMT

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.