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
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;
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.
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"
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.