Struggling with a CASE WHEN Statement

breelloyd — Wed, 24 Apr 2013 04:18:48 GMT

Hi Guys,

I'm using SAS Enterprise Guide 5.1 I'm using the GUI to prepare the report.

I'm transferring my reports from SQL/Excel solutions to SAS.

So here is the SQL that I used:

MAX(CASE

WHEN Cc.ContactTypeID = 5

THEN Cc.ContactValue

ELSE Null END) AS 'Email'

The data basically looks like this:

Cc.Custcode Cc.ContactTypeID Cc.ContactValue

112233 1 0422778899

112233 2 0395874521

112233 5 email@email.com

112244 5 another@email.com

What I'm trying to do is this:

Cc.CustCode Mobile Phone Email

112233 0422778899 0395874521 email@email.com

112244 another@email.com

Here is what I have for the email field:

MAX(CASE WHEN CustomerContact.ContactTypeID = 5

THEN CustomerContact.ContactValue

ELSE .

END)

I'm getting the following error:

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

I've tried to read up on a solution of this- and I've read the issue could have something to do with filters.

I have one filter on (and it's the same filter that I had in the WHERE Clause in my report- limiting to orders which were delivered- so it's unrelated. Removing it makes no difference).

I'm getting to a point where I'm stuck and I couldn't find anything on the SAS forums which solved it for me.

Does anyone have any idea?

Bree

Re: Struggling with a CASE WHEN Statement

breelloyd — Wed, 24 Apr 2013 04:23:51 GMT

Actually- looks like doing this solved it:

MAX(CASE WHEN CustomerContact.ContactTypeID = 5

THEN CustomerContact.ContactValue

ELSE ""

END)

does anyone understand why?

Re: Struggling with a CASE WHEN Statement

Tom — Wed, 24 Apr 2013 05:06:40 GMT

Because " " is a character value like he variable CONTACTVALUE is and . (missing) is a numeric value. That is what the error message was telling you.

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Re: Struggling with a CASE WHEN Statement

breelloyd — Wed, 24 Apr 2013 05:13:17 GMT

That makes sense.

I'm not used to not having a null

Thanks for that though- I like to understand why I'm getting errors, even if I solve it. It'll help me later on if it comes up again.

Thanks for taking the time to respond.

topic Re: Struggling with a CASE WHEN Statement in SAS Enterprise Guide

Struggling with a CASE WHEN Statement

Re: Struggling with a CASE WHEN Statement

Re: Struggling with a CASE WHEN Statement

Re: Struggling with a CASE WHEN Statement