SAS subsetting based on a macro variable

1
votes

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!

2
datesassas-macro

2 Answers

4
votes

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;
1
votes

Your problem is you are formatting the result - don't do that. Remember, macro variables are just creating text, they're not actual variables - they don't have concepts like "formats" (where a regular SAS variable is able to be the numeric date value AND the "pretty" formatted text).

1    %let startDate = %sysfunc(intnx(dtmonth, %sysfunc(datetime()), -1), datetime20.);
2    %put &=startdate.;
STARTDATE=01JUL2015:00:00:00

So:

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

That's illegal, because that's not a datetime value, it's a nice pretty human readable datetime that SQL/SAS looks at and doesn't know what to do with.

You can either drop the format bit from the %sysfunc, or add "..."dt around it to make it a datetime constant.

data existing_table;
  do date=datetime()-86400*50 to datetime() by 86400;
    output;
  end;
run;

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