Variable defined in %let won't work in WHERE statement

[RoddyJ]

Hi all,

I'm automating a report using SAS and have created some %let statements to define dates. I've found that variables defined in the %let statements won't work when used in a WHERE statement in a datastep but will work in an if statement. 

Can anyone explain why this is?

Example code:

%let date1 = 01JAN2018 ;
%let date2 = 01JAN2019 ;

data d2; 
set d1 ;
where "&date1"d =< d1_date < "&date2"d ;
run; 

[Reeza]

it should work fine, something else is going wrong. Post your log from that portion of the code that shows the error. 

Common mistakes are single quotes, not having the date as a literal or having a datetime variable instead of a date.

---

@RoddyJ wrote:

Hi all,

I'm automating a report using SAS and have created some %let statements to define dates. I've found that variables defined in the %let statements won't work when used in a WHERE statement in a datastep but will work in an if statement. 

Can anyone explain why this is?

Example code:

%let date1 = 01JAN2018 ;
%let date2 = 01JAN2019 ;

data d2; 
set d1 ;
where "&date1"d =< d1_date < "&date2"d ;
run; 

---

[Kurt_Bremser]

Use correct syntax:

 85         where "&date1"d =< d1_date < "&date2"d ;
                             _
                             22
                             76
 ERROR: Syntaxfehler bei Verarbeitung der Where-Bedingung.
 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: ein Name, eine Zeichenkette in Hochkommata, 
               eine numerische Konstante, eine Datetime-Konstante, ein fehlender Wert, INPUT, PUT.  
 
 ERROR 76-322: Syntax error, statement will be ignored.

The correct syntax for the "less or equal" operator will work:

data d1;
input d1_date date9.;
datalines;
01jan2017
30aug2018
;

%let date1 = 01JAN2018 ;
%let date2 = 01JAN2019 ;

data d2; 
set d1 ;
where "&date1"d <= d1_date < "&date2"d ;
run; 

Crucial part of the log:

83         data d2;
84         set d1 ;
85         where "&date1"d <= d1_date < "&date2"d ;
86         run;
 
NOTE: There were 1 observations read from the data set WORK.D1.
      WHERE (d1_date>='01JAN2018'D and d1_date<'01JAN2019'D);
NOTE: The data set WORK.D2 has 1 observations and 1 variables.

[hashman · Accepted solution]

@RoddyJ: What @Kurt_Bremser said.

In the WHERE clause (no matter whether in the DATA step or a proc), the < or > operators must preceded the = operator, that is <= and >= are correct, but => and =< are deemed syntax errors. Note that the IF statement in the DATA step doesn't care, and its compiler evaluates all of the above as correct. 

The simplest way around having to remember what must precede what is to use mnemonic operators instead, i.e. LE for <= and GE for >= both in IF and WHERE. Your WHERE statement would be:

where "&date1"d LE d1_date LT "&date2"d ;

In fact, when the interval endpoints are included in the WHERE comparison, it's even simpler to use the BETWEEN operator (alas, not available with the IF statement). In your case, the upper endpoint is excluded, but since you're dealing with SAS dates (i.e. the number of days since the beginning of 1960) here, you can still use:

where d1_date between "&date1"d and "&date2"d - 1 ;

Kind regards

Paul D.     

[PaigeMiller]

So @Kurt_Bremser has pointed out the error, but it might be helpful to follow certain advice before you try to turn code into code with macro variables and/or code with macros.

The advice is to get the code to work on one or two situations without macros and without macro variables. If your code doesn't work without macros and without macro variables, then it will not work if you are using macros and macro variables. If it does work without macros and without macro variables, then you have a good chance it will work with macros and with macro variables.