topic Re: SELECT INTO numerical variable evaluation in SAS Programming

SELECT INTO numerical variable evaluation

khill — Fri, 12 Nov 2010 19:59:36 GMT

Hello,

This seems like a simple problem. When uses the SELECT INTO clause I end up with a character variable than cannot be evaluated numerically. How can this be modified to resolve the evaluation correctly?

%macro t;
PROC SQL NOPRINT;
Select t1.AVG_Denom into :xAvgDenom
From WORK.testdata2 as t1;
;
%put Average is &xAvgDenom;
%if &xAvgDenom < 25 %then
%put &xAvgDenom is Less Than 25;
%else
%put &xAvgDenom is Greater Than 25;
;
%_eg_conditional_dropds(testdata2)
%mend t;
OPTIONS Nomlogic;
Data testdata2;
input AVG_Denom;
;
cards;
112.4118 ;

%t;

Average is 112.4118
112.4118 is Less Than 25 (not in my way of thinking)

Thank you in advance,
John

Re: SELECT INTO numerical variable evaluation

SPR — Fri, 12 Nov 2010 20:16:38 GMT

Hello Khill,

The problem with your code is that all macro %IFs are resolved at compilation phase when AVG_Denom is not yet accessible. Reducing %IFs to datastep IFs solves the problem:
[pre]
%macro t;
proc SQL noprint;
select t1.AVG_Denom into :xAvgDenom
from WORK.testdata2 as t1
;quit;
%put Average is &xAvgDenom;
data _null_;
if &xAvgDenom < 25 then put "&xAvgDenom is Less Than 25";
else put "&xAvgDenom is Greater Than 25";
run;
%mend t;
%t;
[/pre]
Sincerely,
SPR

Re: SELECT INTO numerical variable evaluation

RickM — Fri, 12 Nov 2010 20:17:02 GMT

Try using

%put Average is &xAvgDenom;
%if %sysevalf(&xAvgDenom < 25) %then
%put &xAvgDenom is Less Than 25;
%else
%put &xAvgDenom is Greater Than 25;

Remember that macro variables are considered text so if you are doing any sort of numeric operation (addition, subtraction, boolean comparison) you need to use %eval (for integers) or %sysevalf (for floating point numbers).

Good luck!

Re: SELECT INTO numerical variable evaluation

ArtC — Fri, 12 Nov 2010 22:09:59 GMT

Just to expand on RickM's answer a bit.

The %IF statement:

[pre]%if &x lt 9 %then.....
[/pre]

will perform a numeric comparison if &x contains an integer (there is an implied %EVAL), but a alphabetic comparison if either side is a noninteger. Since the decimal point makes for a noninteger 10.0 lt 9 is true, while 10 lt 9 is false. The %sysevalf forces a numeric comparison. those darn lt signs

Message was edited by: ArtC

Re: SELECT INTO numerical variable evaluation

khill — Sat, 13 Nov 2010 01:32:15 GMT

Rick and Art, thanks for the insight. This is what I needed to get me back on track.

John