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
- /
- Need help undestanding unique CASE END WHEN THEN l...

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

02-28-2017 10:31 AM

I am currently porting report summary SQL from COGNOS Report Sudio to SAS EG. The SQL in the below segment defines a sub-total label for widgets in 2 different grouping categories. I know widget (type) is suppose to resolve to a range of either 1-11 or 13-28 based on its subtype, but I am baffled how this logic works once it finishes the inner most case statement.

Could someone help me understand the logic of the 5th line from the bottom i.e. "**end < 1**" and how it pertains to the remaining 4 lines? I see the obvious logic but it does not make sense to me as applied to the result, specifically the "**end < 1**".

Here is and abridged version of the SQL segment:

case when case when (case when (Widget.SubType in (100, 105, 110, 115)) then 1 when (Widget.SubType in (140, 145)) then 3 ........... ........... when (Widget.SubType in (133, 138)) then 11 when (Widget.SubType in (71, 72)) then 13 when (Widget.SubType in (80, 81, 85, 88)) then 14 ........... ........... when (Widget.SubType in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28 else 999 end < 1) then '1' else '2' end = '1' then 'Widget Types 1 through 11' else 'Widget Types 13 through 28' end

Accepted Solutions

Solution

03-06-2017
02:53 PM

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

Posted in reply to eg_michael

02-28-2017 11:06 AM

Hello,

This is how I understand it :

The part

```
case
when (Widget.SubType in (100, 105, 110, 115)) then 1
when (Widget.SubType in (140, 145)) then 3
...........
...........
when (Widget.SubType in (133, 138)) then 11
when (Widget.SubType in (71, 72)) then 13
when (Widget.SubType in (80, 81, 85, 88)) then 14
...........
...........
when (Widget.SubType in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28
else 999
end
```

computes a value based on SubType. If we call X this value, the next step si to evaluate :

```
case
when X < 1
then '1' else '2'
end
```

So if the value reflecting the SubType is less than 1, we set a new value, say Y, to 1. Y=2 instead.

Now if Y=1, then we return the string 'Widegets Type 1 to 11' and 'Widgets Type 13 to 28' otherwise

Maybe there is a typo and the "<1" should be "<12" ?

All Replies

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

Posted in reply to eg_michael

02-28-2017 10:52 AM

I haven't seen such logic in ANSI SQL, so my guess is that this is Cognos specific logic. If so, contact your Cognos resource for explanation.

Normally, each when should have a condition and I can't see such in the surrounding case-when's.

Is the first mapping used somewhere else?

If not, assign the 1-11 and so on directly and skip all the nestings.

Data never sleeps

Solution

03-06-2017
02:53 PM

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

Posted in reply to eg_michael

02-28-2017 11:06 AM

Hello,

This is how I understand it :

The part

```
case
when (Widget.SubType in (100, 105, 110, 115)) then 1
when (Widget.SubType in (140, 145)) then 3
...........
...........
when (Widget.SubType in (133, 138)) then 11
when (Widget.SubType in (71, 72)) then 13
when (Widget.SubType in (80, 81, 85, 88)) then 14
...........
...........
when (Widget.SubType in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28
else 999
end
```

computes a value based on SubType. If we call X this value, the next step si to evaluate :

```
case
when X < 1
then '1' else '2'
end
```

So if the value reflecting the SubType is less than 1, we set a new value, say Y, to 1. Y=2 instead.

Now if Y=1, then we return the string 'Widegets Type 1 to 11' and 'Widgets Type 13 to 28' otherwise

Maybe there is a typo and the "<1" should be "<12" ?

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

Posted in reply to gamotte

02-28-2017 11:26 AM

*Maybe there is a typo and the "<1" should be "<12"* ?

That would make perfect sense with the logic as I see it ... I will ponder that as well and see if I can validate.

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

Posted in reply to eg_michael

02-28-2017 11:40 AM

The query seems overly complicated though as intermediary calculations are lost.

```
DATA have;
x=110; output;
x=202; output;
x=140; output;
run;
proc sql noprint;
CREATE TABLE want AS
SELECT case when
case when
(case
when (x in (100, 105, 110, 115)) then 1
when (x in (140, 145)) then 3
when (x in (133, 138)) then 11
when (x in (71, 72)) then 13
when (x in (80, 81, 85, 88)) then 14
when (x in (200, 201, 202, 205, 210, 215, 225, 230, 235)) then 28
else 999
end < 12) then '1' else '2'
end = '1'
then 'Widget Types 1 through 11' else 'Widget Types 13 through 28'
end AS TYPE format=$50.
FROM have;
CREATE TABLE want2 AS
SELECT case when x in (100, 105, 110, 115, 140, 145, 133, 138) then 'Widget Types 1 through 11'
else 'Widget Types 13 through 28'
end as TYPE format=$50.
FROM have;
quit;
```