invalid date time constant

[Renee1984]

Issue: I keep getting this error message after I submit SQL statement

*=========> COMMANDS <=================;
%LET BEGDATE = MDY(01,31,2019);

*=========> COMMANDS <=================;

PROC SQL;
CREATE TABLE MOVCODES AS SELECT
A.*,
B.MOVCOD,
B.MOVTIM,
B.MOVDAT FORMAT=MMDDYY10.
FROM R.ORIGINALPLMPOP A LEFT JOIN OPPS.INMT4BC1 B
ON A.ID=B.ID
WHERE B.MOVDAT LE "&BEGDATE"D;
QUIT;

Error:ERROR: Invalid date/time/datetime constant "MDY(01,31,2019)"D.

[Reeza · Accepted solution]

Your generated code would be:

*=========> COMMANDS <=================;
%LET BEGDATE = MDY(01,31,2019);

*=========> COMMANDS <=================;

PROC SQL;
CREATE TABLE MOVCODES AS SELECT
A.*,
B.MOVCOD,
B.MOVTIM,
B.MOVDAT FORMAT=MMDDYY10.
FROM R.ORIGINALPLMPOP A LEFT JOIN OPPS.INMT4BC1 B
ON A.ID=B.ID
WHERE B.MOVDAT LE "MDY(01,31,2019)"D;
QUIT;

That isn't valid SAS syntax, you do not need the quotes or d.

WHERE B.MOVDAT LE &BEGDATE;

would become:

WHERE B.MOVDAT LE MDY(1, 31, 2019);

Or you need to define your begdate different, see example below:

%let begdate = %sysfunc(mdy(1, 31, 2019), date9.);

....

WHERE B.MOVDAT LE "&begdate"d;

which will resolve as:

WHERE B.MOVDAT LE "31Jan2019"d;

---

@Renee1984 wrote:

Issue: I keep getting this error message after I submit SQL statement

*=========> COMMANDS <=================;
%LET BEGDATE = MDY(01,31,2019);

*=========> COMMANDS <=================;

PROC SQL;
CREATE TABLE MOVCODES AS SELECT
A.*,
B.MOVCOD,
B.MOVTIM,
B.MOVDAT FORMAT=MMDDYY10.
FROM R.ORIGINALPLMPOP A LEFT JOIN OPPS.INMT4BC1 B
ON A.ID=B.ID
WHERE B.MOVDAT LE "&BEGDATE"D;
QUIT;

Error:ERROR: Invalid date/time/datetime constant "MDY(01,31,2019)"D.

---

[Renee1984]

worked perfect thank you for your help!!!