Contributor
Posts: 41

# CASE format in Proc SQL

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

Super Contributor
Posts: 359

## Re: CASE format in Proc SQL

[ Edited ]

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;``````
Super User
Posts: 9,840

## Re: CASE format in Proc SQL

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.

Super User
Posts: 13,941

## Re: CASE format in Proc SQL

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"

Discussion stats
• 3 replies
• 125 views
• 1 like
• 4 in conversation