Struggling with a CASE WHEN Statement

[breelloyd]

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

[breelloyd · Accepted solution]

Actually- looks like doing this solved it:

MAX(CASE WHEN CustomerContact.ContactTypeID = 5

THEN CustomerContact.ContactValue

ELSE ""

END)

does anyone understand why?

[Tom]

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.

[breelloyd]

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.