sas - 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;

question from:https://stackoverflow.com/questions/66063760/proc-sql-case-when-expression-result-of-when-clause-2-is-not-the-same-data-ty

Answer 1

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:

1. 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
2. Fortunately, in SAS you only have to choose between character and numeric. In most real databases, you have a dozen of data types.