turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- CASE format in Proc SQL

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2018 05:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FP12

04-04-2018 06:02 AM - edited 04-04-2018 06:05 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FP12

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FP12

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"