BookmarkSubscribeRSS Feed
FP12
Obsidian | Level 7

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

3 REPLIES 3
gamotte
Rhodochrosite | Level 12

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

ballardw
Super User

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"

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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