topic Re: Union error question in SAS Programming

Union error question

Q1983 — Mon, 29 Jun 2020 17:29:49 GMT

This code produces the following error

proc sql;
create table DSHARE_DATA as
SELECT
LN_NO
,LEGACY_COMPANY
,INITIAL_REQ_DT
,ACCOMODATION_TYPE
,CURR_REQ_CHNL
,INVESTOR_GROUP
,LN_1ST_PRIN_BA
,DEL_BUCKET
,FC_STATUS
,BK_STATUS
,LM_STATUS_CD
,LM_STATUS_CHG_DT
,FB_PLAN_STATUS
,FP_CREAT_DT
,FP_PROMISE_QT
,FP_FST_PR_DU_DT
,FP_LST_PR_DU_DT
,ACCRUAL_STATUS
,PR_ALPHA_STATE_CD
,LN_NXT_PMT_DUE_DT
,ASSISTANCE_STAGE
,CASE
WHEN ASSISTANCE_STAGE IN ('FB PLAN NOT SETUP', 'CLIENT CANCELLED ASSISTANCE') THEN 'No'
ELSE 'Yes'
END AS accom_granted

FROM /*D_DataShare.dbo.*/ DATASHAR.Forbearance_HST_Curr

UNION

SELECT
LN_NO
,LEGACY_COMPANY
,INITIAL_REQUEST_DT
,ACCOMMODATION_TYPE
,REQ_CHANNEL
,INVESTOR_INSURER
,LN_1ST_PRIN_BA
,DEL_BUCKET
,FC_STATUS
,BKR_STATUS
,LM_STATUS_CD
,LM_STATUS_CHG_DT
,FP_STAT_CD
,FP_CREAT_DT
,FP_PROMISE_QT
,FP_FST_PR_DU_DT
,FP_LST_PR_DU_DT
,CASE
WHEN CA_ACR_STAT_CD = 'ACCRUING' THEN 'ACCRUAL'
WHEN CA_ACR_STAT_CD = 'NON ACCRUAL' THEN 'NON-ACCRUAL'
ELSE ''
END AS ACCRUAL_STATUS /*dif*/
,PR_ALPHA_STATE_CD
,LN_NXT_PMT_DUE_DT
,ASSISTANCE_STAGE
,CASE
WHEN ASSISTANCE_STAGE IN ('FB PLAN NOT SETUP', 'CLIENT CANCELLED ASSISTANCE') THEN 'No'
ELSE 'Yes'
END AS accom_granted

FROM /*D_DataShare.dbo.*/ DATASHAR.Forbearance_HBBT_Curr

/*);

DISCONNECT FROM DATASHAR;*/ ;

QUIT;

ERROR: Column 15 from the first contributor of UNION is not the same type as its counterpart from the second.

Does this mean every field before the union must be the exact datatype as after the union???

Re: Union error question

mklangley — Mon, 29 Jun 2020 17:39:37 GMT

Yes. See @ballardw's expanded answer to this question: https://communities.sas.com/t5/SAS-Programming/Error-Message-When-performing-Union-All/m-p/588053/highlight/true#M168009

Re: Union error question

Reeza — Mon, 29 Jun 2020 17:39:37 GMT

ERROR: Column 15 from the first contributor of UNION is not the same type as its counterpart from the second.

Does this mean every field before the union must be the exact datatype as after the union???

Yes, the types need to be the same in the queries. You cannot have a variable that's character and numeric in the same column for example.

Re: Union error question

Kurt_Bremser — Mon, 29 Jun 2020 17:42:43 GMT

@Q1983 wrote:

Does this mean every field before the union must be the exact datatype as after the union???

Exactly. Columns in database tables and/or SAS datasets have the same attributes throughout the table, so you must provide the same when creating a new table.