topic Re: Calculated values in PROC SQL in SAS Procedures

Calculated values in PROC SQL

bissjoe — Fri, 03 May 2013 22:42:41 GMT

I have a strange behavior from SAS.

Consider the three following steps:

data F1;

do nom=1 to 10;

x = ceil(ranuni(123)*1000);

c = ( ranuni(123) > 0.4 );

output;

end;

run;

proc sql;

create table F2 as

select

nom,

case

when calculated c2 > 0 then x**2

else 0

end

as x2

from F1;

quit;

proc sql;

create table F3 as

select

nom,

(c)+0 as c3,

case

when calculated c3 > 0 then x**2

else 0

end

as x3

from F1;

quit;

The step creating F2 generates the error message:

ERROR: The following columns were not found as CALCULATED references in the immediate query: c2.

But the step creating F3 does not generate an error.

My example was simplified. In the real context, variable c would be replaced by a parameter's value, which could be a Boolean expression or a numeric variable. In either case, any value not being zero nor null would trigger a TRUE, otherwise a FALSE. This in principle works with a Boolean condition (which returns 1 for true, and 0 for false), except that PROC SQL does not c2 as a CALCULATED value. In fact, the alternative code

proc sql;

create table F2 as

select

nom,

case

when c2 > 0 then x**2 /* CALCULATED is not specified! */

else 0

end

as x2

from F1;

quit;

works. The problem is, I want a common codebase for both cases for my parameter.

Re: Calculated values in PROC SQL

AncaTilea — Fri, 03 May 2013 23:11:42 GMT

Hi.

I think the issues is that in the first instance - code below, you are not CALCULATING anything, you are just saving c as c2....but if you were to use sum(c) as c2, or whatever other function...then the newly created c2 variable would actually hold a calculated value.Do you know what I mean?

proc sql;

create table F2 as

select

nom,

(c) as c2,

case

when calculated c2 > 0 then x**2

else 0

end

as x2

from F1;

quit;

I hope this helps.

Re: Calculated values in PROC SQL

bissjoe — Sat, 04 May 2013 00:42:16 GMT

I agree with you. It's only a copy of the value. But yet, since I want to allow for two possible syntaxes for my parameter, I would have liked to use a single code for managing the two syntaxes. Now, if I want to be robust, I will have to use

just to make sure, but this does not seem to me very elegant, more a workaround.

Re: Calculated values in PROC SQL

PGStats — Sat, 04 May 2013 01:14:31 GMT

The only other alternative I can see is to repeat the expression for calculating c2 twice in your query :

proc sql;

create table F2 as

select

nom,

<Expression> as c2,
case

when (<Expression>) > 0 then x**2

else 0

end

as x2

from F1;

quit;

It is not the pinnacle of elegance but it doesn't look like a quirky trick either, IMHO.

Re: Calculated values in PROC SQL

Tom — Sat, 04 May 2013 01:43:48 GMT

Forget the CALCULATED keyword, just repeat the expression.

(c) as c2,

case

when (c) > 0 then x**2

Re: Calculated values in PROC SQL

bissjoe — Sat, 04 May 2013 03:11:35 GMT

That is indeed one of the workarounds I consider. The other is to write

proc sql;

create table F3 as

select

nom,

(c)+0 as c3,

case

when calculated c3 > 0 then x**2

else 0

end

as x3

from F1;

quit;

I'm still not decided which I should choose. The second one has the (dubious?) advantage to be faster, since I do not have to evaluate again the value c, but it may be a very, very tiny performance penalty to use the second one (only very complex Boolean conditions would impede on performance).