Issue in resolving date variable in where clause

[archana]

Old query

Data x ;

set y;

where Offer_St_dt >= '01OCT2011'd;

run;

I am trying to replace the above query with

New Query

%let year1 = %STR(%')%sysfunc(intnx(month,%sysfunc(today()),-12),date9.)%STR(%');

Data x ;

set y;

where Offer_St_dt >= &year1;

run;

I am getting error in the above case as

ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, +, -, :, INPUT, NOT, PUT, ^, ~.

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

[slchen]

Change to:

%let year1 =%sysfunc(intnx(month,%sysfunc(today()),-12),date9.);

[archana]

It is not working. i am getting the same error.

[Quentin]

In your macro approach, you forgot the d at the end of the date literal, and you need to manually %unquote() the macro variable (this is an old macro language bug, it should unquote it for you but sometime it doesn't... when code from MPRINT looks right but fails, try %unquote().   Ksharp's non macro approach works for me as well.  Code below.

%let year1 = %STR(%')%sysfunc(intnx(month,%sysfunc(today()),-12),date9.)%STR(%')d; 

data have;
  Offer_St_dt="01Jan2012"d; output;
  Offer_St_dt="01Jan2014"d; output;
run;

%put &year1;

data want ;
  set have;
  where Offer_St_dt >= %unquote(&year1);
run; 

data want2 ;
  set have;
  where Offer_St_dt >=intnx('month',today(),-12)   ;
run;

[Ksharp]

Why not write it in data step?

Data x ;

set y;

where Offer_St_dt >=intnx('month',today(),-12)   ;

run;

[jakarman]

A more easier approach would be setting some global macro variables by a sas-datastep.
When this is a standard approach it could be a standard setting part of sas start.

By that you can verify the content of the macro var. Leave it as text  and put the quotes and d indication when needed

[Tom]

Why are you using single quotes instead of double quotes?  Macro references do not resolve inside of single quotes.

%let year1 = "%sysfunc(intnx(month,%sysfunc(today()),-12),date9.)"d ;

Data x ;

  set y;

  where Offer_St_dt >= &year1;

run;