PROC SQL: CASE WHEN EXPRESSION - Result of WHEN clause 2 is not the same data type as the preceding results

Question

I'm trying to create and fill a column: 'FLAG' ,using data from two tables: carteira_base30 and TRIGGERS_21.

Whenever carteira_base30.Data_ref is 31DEC2015 (format: DATE9.) it should retrieve info from TRIGGERS_21.D31DEC2015 (column in number format with values 0 and 1).

The following code returns me: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results. Can you help me out?

PROC SQL;
CREATE TABLE carteira_base31 AS
    SELECT  x.*,
            CASE x.Data_ref 
            WHEN '31DEC2015'd THEN y.D31DEC2015
            ELSE 'Other'
            END AS 'FLAG'n
    FROM carteira_base30 x
    LEFT JOIN TRIGGERS_21 y
    ON x.NIF = y.NIF;
QUIT;

What about the error is confusing? if `D31DEC2015` is a number, then it's not text, so you can't mix and match then as outputs of the `case`. Just cast said number to text if you want text output. — underscore_d, Feb 05 '21 at 13:03
It is not a *CLAUSE*, but a `CASE EXPRESSION` : everything between `CASE` and `END` is just replaced by some expression. This expression can be NULL, or have a value. It always has a type. It **must** have the correct type. — wildplasser, Feb 05 '21 at 13:23

Dirk Horsten · Accepted Answer · 2021-02-05T16:08:06.400

All relational tables (1) have columns with a type, that is all rows must have the same type (2) of data in a column. So sorry, you cannot store a number and a text in the same column and you will have to make up your mind.

You might choose for characters and code for instance

CREATE TABLE carteira_base31 AS
    SELECT  x.*,
            CASE x.Data_ref 
                WHEN '31DEC2015'd THEN put(y.D31DEC2015, 1.)
                ELSE 'Other'
            END AS 'FLAG'n
    FROM carteira_base30 x
    LEFT JOIN TRIGGERS_21 y
    ON x.NIF = y.NIF;

Or You might choose for numeric and code for instance

CREATE TABLE carteira_base31 AS
    SELECT  x.*,
            CASE x.Data_ref 
                WHEN '31DEC2015'd THEN y.D31DEC2015
                ELSE .
            END AS 'FLAG'n
    FROM carteira_base30 x
    LEFT JOIN TRIGGERS_21 y
    ON x.NIF = y.NIF;

where . means "missing".

You can even apply a format to that. Then your result will look as if you put text in a numeric field, though you didn't.

proc format;
    value miss_other . = 'other';
run;
proc sql;
CREATE TABLE carteira_base31 AS
    SELECT  x.*,
            CASE x.Data_ref 
                WHEN '31DEC2015'd THEN y.D31DEC2015
                ELSE .
            END AS 'FLAG'n format = miss_other.
    FROM carteira_base30 x
    LEFT JOIN TRIGGERS_21 y
    ON x.NIF = y.NIF;

Disclaimer: not sure I got the syntax of the value statement right.

Remarks:

SAS datasets are relational tables in the sense that they contain metadata about their structure and you can relate them with sql, in spite of them not residing in a database
Fortunately, in SAS you only have to choose between character and numeric. In most real databases, you have a dozen of data types.

Thanks a lot for the effort Dirk. I just changed 'Other' for a numerical value and it worked perfectly :) I haven't realized that the error was referring to it. Cheers, Miguel — Miguel Saraiva, Feb 05 '21 at 18:59

PROC SQL: CASE WHEN EXPRESSION - Result of WHEN clause 2 is not the same data type as the preceding results

1 Answers1