04-04-2018 05:25 AM
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?
04-04-2018 06:02 AM - edited 04-04-2018 06:05 AM
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;
04-04-2018 06:12 AM
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.
04-04-2018 11:10 AM
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"