error with date9 field set equal to macro variable in where statement

Reply
Occasional Contributor
Posts: 5

error with date9 field set equal to macro variable in where statement


data dates; daydate1=intnx('day',today(),-1,'B'); FORMAT daydate1 date9.; run; proc sql; select daydate1 into :daydate1 FROM dates; quit; proc sql; connect to sqlsvr as sqlsvr (dsn=sqlserverp authdomain=sqlauthp); create table work.submit as select * from connection to sqlsvr (select ID, store_number, min(rpt_dt) as Submit_Dt from AppStats where store_number <> '' group by ID, store_number order by ID asc); quit; proc sql; create table subday as select input(store_number,4.) as store, count(ID) as Submits from submit where submit_dt = &daydate1 group by store_number; quit;

I'm getting an error message on the macro variable equaling the submit date, but I have no idea why - submit_dt is date9. format and informat.  Any ideas? Using SAS EG

 

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "DAYDATE1".
18 24NOV2016
_______
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET,
GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Respected Advisor
Posts: 4,138

Re: error with date9 field set equal to macro variable in where statement

Your macro variable "daydate1" contains the formated date string and not the internal SAS date value (=digits representing the number of days since 1/1/1960).

 

NOTE: Line generated by the macro variable "DAYDATE1".
18 24NOV2016

 

So your where clause:

where submit_dt = &daydate1

resolves to:

where submit_dt = 24NOV2016

which is incorrect SAS syntax

 

You can get around this by doing one of the following:

where submit_dt = "&daydate1"d

or 

where submit_dt = intnx('day',today(),-1,'B')

or

proc sql noprint;
select strip(put(daydate1,f16.)) into :daydate1 FROM dates;
quit;

or

data dates;
  daydate1=intnx('day',today(),-1,'B');
  FORMAT daydate1 date9.;
  call symputx('daydate1',put(daydate1,f16.));
run;

 

Ask a Question
Discussion stats
  • 1 reply
  • 127 views
  • 0 likes
  • 2 in conversation