topic Re: CASE format in Proc SQL in SAS Programming

CASE format in Proc SQL

FP12 — Wed, 04 Apr 2018 09:25:23 GMT

Hi,

I have three columns A and B, both numeric, and C = ABS(A-B)

Sometimes A can be equal to 0. The same for B. They can also be both equal to 0 at the same time.So does C then.

I want to create a column D as the percentage of difference of B in comparison with A.

The mathematical formula is easy:

ROUND(C * 100 / A,0.01)

But you will have noticed that if A and/or C equal to 0 it won't work.

Then I do something like this:

if A = 0 
   if C = 0 then 0
   Else "INF"
Else ROUND(C* 100 / A,0.01)

"INF" stands for infinity here

In term of SAS that means:

CASE WHEN t1.A = 0 THEN
      CASE WHEN t1.C = 0 THEN 0
      ELSE "INF" END
ELSE ROUND(t1.C * 100 / t1.A,0.01)
END AS D

But when I do this I have the following error:

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Do you know how I should do it?

Thanks

Re: CASE format in Proc SQL

gamotte — Wed, 04 Apr 2018 10:05:43 GMT

Hello,

As C is on the numerator, you shouldn't have to test its value.

Also, D is a numeric variable so "INF" is not a valid value.

You could set it to . and add an indicator variable as follows :

proc sql;
    SELECT a, b, abs(a-b) AS c, 
           CASE WHEN a=0 THEN . ELSE round(CALCULATED c*100/a,0.01) END AS d,
           CASE WHEN a=0 THEN 1 ELSE 0 END AS INF
    FROM test;
quit;

Re: CASE format in Proc SQL

RW9 — Wed, 04 Apr 2018 10:12:18 GMT

Numeric column cannot contain "INF" as that is text. You could create a character version as output:

ELSE put(ROUND(t1.C * 100 / t1.A,0.01),best.)

But then the rest of the column becomes a bit useless. A better method is to create a format, and a specific number represents INF, e.g. if -999:

proc format;
  value inf
    -999="INF";
run;

... end as d format=inf.
...

So the number underneath would remain as -999, but would be displayed formatted as INF.

Re: CASE format in Proc SQL

ballardw — Wed, 04 Apr 2018 15:10:46 GMT

I might suggest using the DIVIDE function which will handle division by 0 and will assign special missing values for the division and not generate an error or warning message though in the Round function result you will get missing values as a result.

ROUND(C * divide(100,/ A),0.01)

Or use the special missing .I (yes there is period that is part of the value) instead to TEXT "inf"