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,
(c) as c2,
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,
(c) as c2,
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.
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.
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
(c) + 0
just to make sure, but this does not seem to me very elegant, more a workaround.
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.
PG
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).
2025 SAS Hackathon: There is still time!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Register Now
