Data Item CASE statement limitations in SAS Information Map Studio (version 4.4 M7)

[Eitan123]

Hey,

I've been trying to create a data item using the following code (X is an existing data item):
CASE WHEN (<<root.X>> >= 0 AND <<root.X>> <= 30) THEN 1
WHEN (<<root.X>> > 30 AND <<root.X>> <= 70) THEN 2
WHEN (<<root.X>> > 70 AND <<root.X>> <= 100) THEN 3
WHEN (<<root.X>> > 100 AND <<root.X>> <= 200) THEN 4
WHEN (<<root.X>> > 200 AND <<root.X>> <= 300) THEN 5
WHEN (<<root.X>> > 300 AND <<root.X>> <= 400) THEN 6
WHEN (<<root.X>> > 400 AND <<root.X>> <= 500) THEN 7
WHEN (<<root.X>> > 500 AND <<root.X>> <= 600) THEN 8
WHEN (<<root.X>> > 600 AND <<root.X>> <= 700) THEN 9
WHEN (<<root.X>> > 700 AND <<root.X>> <= 800) THEN 10
WHEN (<<root.X>> > 800 AND <<root.X>> <= 900) THEN 11
WHEN (<<root.X>> > 900 AND <<root.X>> <= 1000) THEN 12
ELSE 13
END

After trying to use my new data item in CIS in a campaign, I got the following error (known in SQL with CASE statements as well):
ERROR: Open cursor error: ICommand::Execute failed. : Case expressions may only be nested to level 10.: Statement(s) could not be prepared.

Is there any way around this? preferably within SAS Information Map Studio.

I have a SQL DB table ready for more complicated data items which Information Maps can not handle, but that would only be a last resort for me.

Just to clarify -  I didn't leave any code out, the entire code is added to this topic. I know this code is not nested and that the error is about nested CASE statements, that's the main issue and the reason i opened this topic.

[PhilC]

I got A's in Algebra, so I love it when I get to use y=mx+b!  Check my work, because my teachers were push overs.

CASE 
  WHEN (<<root.X>> >= 0 AND <<root.X>> <= 30)   THEN 1
  WHEN (<<root.X>> > 30 AND <<root.X>> <= 70)   THEN 2
  WHEN (<<root.X>> > 70 AND <<root.X>> <= 1000) 
    THEN ceil(<<root.X>>/100)+2
  ELSE 13
END

[Eitan123]

@PhilC

I copy+pasted your code. Doesn't compile.

[PhilC]

Sorry it didn't work.

[Tom]

I don't see how that code is generating that error.

The error is talking about NESTED case statements such as:

case when (...)
  case when (...)
     case when (...) 
      ...
     end
  end
end

[ballardw]

---

@Tom wrote:

I don't see how that code is generating that error.

The error is talking about NESTED case statements such as:

case when (...)
  case when (...)
     case when (...) 
      ...
     end
  end
end

---

OP pretty obviously did not show a complete step of anything. So perhaps hiding the multiple nesting levels actually used assuming the content of this single one was the problem since it has more than 10 cases?

[Eitan123]

I don't know what OP is, but just to clarify - I know my code does not include nested CASE statements, that's why i'm having trouble reasoning with it. I didn't leave any code out, the entire code is added to this topic.

[PhilC]

I searched the web and found another SQL coding help request not SAS, but SQL. Same case structure, same mention of nesting. I assume the word nesting must allude to the compile time conversion of the case statement to a nested if-then structure...

(OP original post or original poster)

[Eitan123 · Accepted solution]

Perhaps there's a way to split the amount of WHEN's in Information Maps inside the data item's expression?

in SQL I would use COALESCE to pick the first none-null value that comes up so splitting is possible there, but I don't know any way to do so in SAS:

SELECT COALESCE(
CASE SUBSTRING(p.Name, 1, 1)
    WHEN 'a' THEN '1' 
    WHEN 'b' THEN '2' 
    WHEN 'c' THEN '3' 
    WHEN 'd' THEN '4' 
    WHEN 'e' THEN '5' 
    WHEN 'f' THEN '6' 
    WHEN 'g' THEN '7' 
    WHEN 'h' THEN '8' 
    WHEN 'i' THEN '9' 
    ELSE NULL
END,
CASE SUBSTRING(p.Name, 1, 1)
    WHEN 'j' THEN '10' 
    WHEN 'k' THEN '11'  
END)
FROM dbo.AdventureWorks.Sample AS p

[PhilC]

I have no experience with Information Map Studio, I have used Enterprise Guide, which has a GUI interface to ”write” SQL. The video I pulled down makes me think EG and IMP are similar. Your last 10 cases on the statement are linear in nature, with the combination of the ceiling function. I can't see why my idea won't work. When I get back to my computer in the morning, I'll look To see if I can offer something more.

[Eitan123]

Your idea didn't work because I tried it on IMS, not EG.

My client asks these data items to be fully created in IMS, so EG is not an option. Thank you for replying several times...!

[Kurt_Bremser]

I guess that the outer shell created by CIS already contains a lot of CASEs, and yours is the straw that breaks the camel‘s back.
One of the inherent deficiencies of pointy-clicky tools: you lose control over the final code.

[Eitan123]

However, there might be a way around this. in SQL, I would split my WHEN's as follows:

 select ID,
coalesce( CASE
when X between 0 and 30 then 1
when X between 31 and 70 then 2
when X between 71 and 100 then 3
when X between 101 and 200 then 4
when X between 201 and 300 then 5
when X between 301 and 400 then 6
when X between 401 and 500 then 7
when X between 501 and 600 then 8
when X between 601 and 700 then 9 end,
CASE
when X between 701 and 800 then 10
when X between 801 and 900 then 11
when X between 901 and 1000 then 12
when X > 1000 then 13
end )
from Y

Is there a way to do the same in data item expressions?

[PhilC]

Sorry!, I though you could use functions.  How peculiar.

Can you implement subqueries?  I'm thinking of this table and this subquery:

select min(Z) from Z where <<root.X>> > minX

Table: Z
minX	Z
0	1
30	2
70	3
100	4
200	5
300	6
400	7
500	8
600	9
700	10
800	11
900	12
1000	13

[Eitan123]

I can't use subqueries, but as it seems CEILING doesn't work and COALESCE does work, and solves the problem without a need for tables.
Thank you for your time, I highly appreciate the effort! 🙂