If and or conditions in SAS

[sathya66]

I am trying to get the output if  if conditions are met then output something or print table but Conditions doesn't work. Even below condition is true or not true it always giving true output. Tried with different true or false conditions but it prints always true condition which is not right. Am I missing some logic. See the attached file for data.

proc import datafile="/test/test.xls "
out=test dbms=xls replace;
run;


proc sql noprint;
	select Despatches into :COL from test where inflight_type = 'Collect' ;
quit;

proc sql noprint;
	select a2_Sales into :COL2 from test where inflight_type = 'Collect' ;
quit;

proc sql noprint;
	select Despatches into :POST from test where inflight_type = 'Post';
quit;

proc sql noprint;
	select a2_Sales into :POST2 from test where inflight_type = 'Post' ;
quit;

proc sql noprint;
	select Despatches into :DEPOT from test where inflight_type = 'Depot'; 
quit;	

proc sql noprint;
	select a2_Sales into :DEPOT2 from test where inflight_type = 'Depot' ;
quit;

%put &COL &COL2 &POST &POST2 &DEPOT &DEPOT2;

%macro test;

%if (&COL>1000 or &COL2>5 or &POST>0 or &POST2>0 or &depot>1000 or &depot2>1 ) %then %do;
%put "doesn't exist";
%end;
%else %do;
proc print data=sashelp.class;
run;
%end;
%mend;
%test;

[Kurt_Bremser]

&depot resolves to 130, which is larger than 1. Did you mean to use &depot2 in your last sub-condition?

[sathya66]

Sorry yes, it is &depot2

[PaigeMiller]

Most of us refuse to download Excel files. So we cannot see your data. If you want to provide data for us, the only acceptable way is via working SAS data step code (see here for examples and instructions).

Please show us the output from this command:

%put &COL &COL2 &POST &POST2 &DEPOT &DEPOT2;

[sathya66]

%put COl=&COL ;
COl= 833.49
%put Col2=&COL2;
Col2=0.68
%put POST=&POST ;
POST=0
%put POST2=&POST2 ;
POST2=0
%put DEPOT=&DEPOT ;
DEPOT= 130
%put DEPOT2=&DEPOT2;
DEPOT2=0.13

[Kurt_Bremser]

So none of your conditions is met, and sashelp.class is printed.

[sathya66]

My output is different.
See my output below it is printing "doesn't exist"

28         %put &COL &COL2 &POST &POST2 &DEPOT &DEPOT2;
833.49            0.68               0               0             130            0.13
29         %macro test;
30         
31         %if (&COL>1000 or &COL2>5 or &POST>0 or &POST2>0 or &depot>1000 or &depot2>1 ) %then %do;
32         %put "doesn't exist";
33         %end;
34         %else %do;
35         proc print data=sashelp.class;
36         run;
37         %end;
38         %mend;
39         %test;
"doesn't exist"

[PaigeMiller · Accepted solution]

You need to use %SYSEVALF to perform non-integer arithmetic

Simplified Example

%let col=833.49;

%macro test;
%if %sysevalf(&COL>1000) %then %do;
%put "doesn't exist";
%end;
%else %do;
proc print data=sashelp.class;
run;
%end;
%mend;
%test

Here's what you see in the log from the above code

MPRINT(TEST):   proc print data=sashelp.class;
MPRINT(TEST):   run;

but if you remove the %SYSEVALF, this is what you get

%macro test;
%if &COL>1000 %then %do;
%put "doesn't exist";
%end;
%else %do;
proc print data=sashelp.class;
run;
%end;
%mend;
%test

MLOGIC(TEST):  %IF condition &COL>1000 is TRUE
MLOGIC(TEST):  %PUT "doesn't exist"
"doesn't exist"

[Quentin]

Just to add a bit to Paige's correct answer... The %IF statement uses the %EVAL function to evaluate expressions.  %EVAL can evaluate both numeric expressions and character expressions.  But there is no way for you to tell %EVAL to do a numeric comparison or a character comparison.  So %EVAL needs to look at the expression, and decide whether to do a numeric comparison or character comparison.  It has simple rules to make this decision, basically, if the operands are all numbers, do a numeric comparison, else do a character comparison.  Unfortunately, %EVAL does not know that a decimal point can be part of a number. So if you do:

%put %eval(2 > 10.0) ; %*True- character comparison;

you get 1 (true), because it's doing a character (alphabetical) comparison.  It's the equivalent of:

data _null_;
  if "2">"10.0" then put "2 is greater than 10.0" ;
run;

%SYSEVALF also has to decide whether to do a numeric or character expression, but it knows that a decimal point can be part of a number.  So 

%put %sysevalf(2>10.0) ; %*False - numeric comparison;

returns false because it does a numeric comparison.  If you add an extra dot to 10.0, %sysevalf sees that 10.0. is not a number, and changes to a character comparison

%put %sysevalf(2>10.0.) ; %*True- character comparison;