DATA Step, Macro, Functions and more

Need help undestanding unique CASE END WHEN THEN logic

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Need help undestanding unique CASE END WHEN THEN logic

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
Regular Contributor
Posts: 194

Re: Need help undestanding unique CASE END WHEN THEN logic

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" ?

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Need help undestanding unique CASE END WHEN THEN logic

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
Regular Contributor
Posts: 194

Re: Need help undestanding unique CASE END WHEN THEN logic

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" ?

Occasional Contributor
Posts: 9

Re: Need help undestanding unique CASE END WHEN THEN logic

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.

Regular Contributor
Posts: 194

Re: Need help undestanding unique CASE END WHEN THEN logic

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 149 views
  • 0 likes
  • 3 in conversation