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
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" ?
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.
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" ?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.