Calculated values in PROC SQL

[bissjoe]

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.

close

[AncaTilea]

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.

[bissjoe]

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.

close

[PGStats]

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

[bissjoe]

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).

close

[Tom]

Forget the CALCULATED keyword, just repeat the expression.

(c) as c2, 

    case

      when (c) > 0 then x**2