Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...