DATA Step, Macro, Functions and more

Issue in resolving date variable in where clause

Reply
Contributor
Posts: 31

Issue in resolving date variable in where clause

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.

Super Contributor
Posts: 275

Re: Issue in resolving date variable in where clause

Change to:

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


Contributor
Posts: 31

Re: Issue in resolving date variable in where clause

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

PROC Star
Posts: 1,322

Re: Issue in resolving date variable in where clause

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;
Super User
Posts: 10,018

Re: Issue in resolving date variable in where clause

Why not write it in data step?

Data x ;

set y;

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

run;

Trusted Advisor
Posts: 3,211

Re: Issue in resolving date variable in where clause

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

---->-- ja karman --<-----
Super User
Super User
Posts: 7,039

Re: Issue in resolving date variable in where clause

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;

Ask a Question
Discussion stats
  • 6 replies
  • 1632 views
  • 6 likes
  • 6 in conversation