Assign a date to a macro variable and use it to subset the data from a dataset

[chandan_mishra]

Hello Everyone

I am trying to assign a date to a macro variable and using that macro variable to subset a data from a dataset. It's throwing me an error. Here is the code:

/*Assigning the macro variable the 27th Jan 2018 date*/
%let date_comp= %SYSFUNC(INTNX(DAY,%SYSFUNC(TODAY()),-3),date9.);
%put &date_comp.;
27JAN2018

DATA want(where=( Ship_Date >=&date_comp.));
set have;
run;

The want data is throwing me an error. This is the error.

data want (where=(ship_date >=&date_comp.));
NOTE: Line generated by the macro variable "DATE_COMP".
1 27JAN2018
-------
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>,
=, >, >=, AND, EQ, GE, GT, LE, LT, NE, OR, ^=, |, ||, ~=.

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

3721 set have;
3722 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

ERROR: Syntax error while parsing WHERE clause.

Please help me with this. 

Thanks

Chandan Mishra

[PaigeMiller · Accepted solution]

DATA want(where=( Ship_Date >="&date_comp"d));
set have;
run;

Formatted SAS dates which are text must be enclosed in single or double quotes, followed by the letter D (and since you are using a macro variable, it must be double quotes).

You can save yourself a bunch of coding by not formatting the macro variable as date9., just leave it as an integer.

[RW9]

DATA want(where=( Ship_Date >= "&date_comp."d));

Is the update you need, however why bother at all, it does nothing more than:

data want;
  set have;
  where ship_date > today()-3;
runl

Except making the code far harder to read, maintain and debug.  KISS - Keep It Simple Smart - is a mantra you should as programmer live by!

[chandan_mishra]

Hello 

Thanks for your amazing reply. I am using a master file to run all the separate codes and want data is in the separate code. It's only because I don't have to change the code every time in the separate code, I used a macro variable in the master file and using it in the separate code. But I understood what you want to say. Thank you.

Thanks

Chandan Mishra

[art297]

Try:

DATA want(where=( Ship_Date >="&date_comp."d));

The macro variable, itself, only contains text.

Art, CEO, AnalystFinder.com