SAS subsetting based on a macro variable

Question

I have created a macro variable which is the datetime at the time the program is run less one month, formatted to datetime20:

%let startDate = %sysfunc(intnx(dtmonth, %sysfunc(datetime()), -1), datetime20.);

This part works correctly (if I run it right now, it returns 01JUL2015:00:00:00), but what I want to do is subset a dataset based on this date in a PROC SQL statement. Basically I want to keep everything where the date occurs in the last month. My code is:

proc sql;
create table work.last_month as
select * from work.existing_table
where date >= &startDate.;
quit;

The column "date" and the variable "startDate" are both of type 'datetime20', but it is still throwing an error:

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

I don't know why it is throwing this error. What am I doing wrong?

Thanks!

Answer 1

The macro processor is a code generator so your code is generating

proc sql;
create table work.last_month as
select * from work.existing_table
where date >= 01JUL2015:00:00:00;
quit;

This isn't valid SAS code. You can either use the SAS datetime number rather than convert/format it to a datetime value, or create a date literal for comparison. To create a date literal, enclose the macro variable in quotes and end it with dt to indicate a datetime value.

proc sql;
create table work.last_month as
select * from work.existing_table
where date >= "01JUL2015:00:00:00"dt;
quit;

To create a SAS datetime numeric value, remove the format in the %sysfunc().

%let startDate = %sysfunc(intnx(dtmonth, %sysfunc(datetime()), -1));

proc sql;
create table work.last_month as
select * from work.existing_table
where date >= &startDate.;
quit;