topic Re: Issue in resolving date variable in where clause in SAS Programming

Issue in resolving date variable in where clause

archana — Fri, 02 May 2014 14:00:49 GMT

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.

Re: Issue in resolving date variable in where clause

slchen — Fri, 02 May 2014 14:05:45 GMT

Change to:

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

Re: Issue in resolving date variable in where clause

Ksharp — Fri, 02 May 2014 14:06:52 GMT

Why not write it in data step?

Data x ;

set y;

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

run;

Re: Issue in resolving date variable in where clause

archana — Fri, 02 May 2014 14:09:21 GMT

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

Re: Issue in resolving date variable in where clause

Quentin — Fri, 02 May 2014 14:21:31 GMT

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;

Re: Issue in resolving date variable in where clause

jakarman — Fri, 02 May 2014 15:29:34 GMT

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

Re: Issue in resolving date variable in where clause

Tom — Fri, 02 May 2014 15:53:21 GMT

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;