Result of WHEN clause 2 is not the same data type as the preceding results.

VALLY — Fri, 10 Sep 2021 09:16:14 GMT

Good day expects.

am running the below query however am getting this error messasge (Result of WHEN clause 2 is not the same data type as the preceding results.)

PROC SQL NOPRINT;
CREATE TABLE WRK.BRACC_CAPITAL AS

SELECT DISTINCT A.AccNrSer
, A.NEW_ACC
, A.BP_ID
, A.OPENORCLOSED
, A.DATE_OPENED
/* , B.ACC_NUM_SERIAL_INV_NUMERIC */
, B.DISPOSAL_ACCNRSER
, B.DISPOSAL_ACCOUNT_NUM
, B.SER_NUM
, B.DISPOSAL_BRANCH
, B.DISPOSAL_INSTRUCTION AS CAPITAL_DISPOSAL
, C.ACNT_NUMBER AS ACCLOC_ACC_NUM
, C.ACNT_SOURCE_SYSTEM
, CASE
WHEN C.ACNT_SOURCE_SYSTEM = 1 THEN D.BP_ID
WHEN C.ACNT_SOURCE_SYSTEM = 110 THEN E.BP_ID
END AS DISPOSAL_BP
, CASE
WHEN C.ACNT_SOURCE_SYSTEM = 1 THEN "BRACC NOMINATION"
WHEN C.ACNT_SOURCE_SYSTEM = 110 THEN "SAP NOMINATION"
ELSE "AGENT BANK" END AS SYSTEM

FROM WRK.FINAL_BRACC_STAGE_02 AS A

LEFT JOIN VBM.CAPITAL_DISPOSAL AS B ON A.ACCNRSER = B.ACC_NUM_SERIAL_INV_NUMERIC

LEFT JOIN DTA.ACCLOC AS C ON B.DISPOSAL_ACCOUNT_NUM = C.ACNT_NUMBER

LEFT JOIN WRK.FINAL_BRACC_STAGE_02 AS D ON B.DISPOSAL_ACCNRSER = D.ACCNRSER

LEFT JOIN FIN.FINAL_SAP_ALL AS E ON B.DISPOSAL_ACCOUNT_NUM = E.ACCNRSER

WHERE A.OPENORCLOSED = "Open"
AND
B.DISPOSAL_ACCNRSER <>.
;
QUIT ;

below is the log.

1 The SAS System 09:57 Friday, September 10, 2021

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='TRANSFORM_BRACC_BPIND';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET
8 ! _SASPROGRAMFILE='C:\Users\a209457\AppData\Local\Temp\1\scp06576\SAS\data\RiskDataManagement\data\NWOW\data\VBM\BRACC\03_S
8 ! cripts\BRACC_Model\TRANSFORM_BRACC_BPIND.sas';
9 %LET _SASPROGRAMFILEHOST='40165JNBCRM617L';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;

26 PROC SQL NOPRINT;
27 CREATE TABLE WRK.BRACC_CAPITAL AS
28
29 SELECT DISTINCT A.AccNrSer
30 , A.NEW_ACC
31 , A.BP_ID
32 , A.OPENORCLOSED
33 , A.DATE_OPENED
34 /* , B.ACC_NUM_SERIAL_INV_NUMERIC */
35 , B.DISPOSAL_ACCNRSER
36 , B.DISPOSAL_ACCOUNT_NUM
37 , B.SER_NUM
38 , B.DISPOSAL_BRANCH
39 , B.DISPOSAL_INSTRUCTION AS CAPITAL_DISPOSAL
40 , C.ACNT_NUMBER AS ACCLOC_ACC_NUM
41 , C.ACNT_SOURCE_SYSTEM
42 , CASE
43 WHEN C.ACNT_SOURCE_SYSTEM = 1 THEN D.BP_ID
44 WHEN C.ACNT_SOURCE_SYSTEM = 110 THEN E.BP_ID
45 END AS DISPOSAL_BP
46 , CASE
47 WHEN C.ACNT_SOURCE_SYSTEM = 1 THEN "BRACC NOMINATION"
48 WHEN C.ACNT_SOURCE_SYSTEM = 110 THEN "SAP NOMINATION"
49 ELSE "AGENT BANK" END AS SYSTEM
50
51 FROM WRK.FINAL_BRACC_STAGE_02 AS A
52
53 LEFT JOIN VBM.CAPITAL_DISPOSAL AS B ON A.ACCNRSER = B.ACC_NUM_SERIAL_INV_NUMERIC
2 The SAS System 09:57 Friday, September 10, 2021

71 %LET _CLIENTPROJECTPATHHOST=;
72 %LET _CLIENTPROJECTNAME=;
73 %LET _SASPROGRAMFILE=;
74 %LET _SASPROGRAMFILEHOST=;
75
76 ;*';*";*/;quit;run;
77 ODS _ALL_ CLOSE;
78
79
80 QUIT; RUN;
81

Re: Result of WHEN clause 2 is not the same data type as the preceding results.

Shmuel — Fri, 10 Sep 2021 09:25:09 GMT

If data types are not equal, it means that one side is numeric when the other is chat type.

Use the input() function to convert the char type variable into numeric, thus the comparison will be numeric.

Re: Result of WHEN clause 2 is not the same data type as the preceding results.

Kurt_Bremser — Fri, 10 Sep 2021 09:27:25 GMT

Maxim 3: Know Your Data.

Looks like d.bp_id and e.bp_id are of different types.

And a few hints for your coding future:

Consistent indentation makes code readable.

SAS does perfectly well understand lowercase code, so there's no need to shout at it. And lowercase is much easier to read.

And use this button to post logs:

and the "little running man" right next to it for SAS code. This will keep any formatting you have done; the main posting window sees everything as simple HTML text and removes (e.g) leading blanks from lines, or converts multiple blanks/tabs to one single "whitespace".

Re: Result of WHEN clause 2 is not the same data type as the preceding results.

Tom — Fri, 10 Sep 2021 12:50:26 GMT

The error message is very specific about what the trouble is.

You only have two CASE clauses. And the second one has string constants. So the issue is with the first one.

, CASE
WHEN C.ACNT_SOURCE_SYSTEM = 1 THEN D.BP_ID
WHEN C.ACNT_SOURCE_SYSTEM = 110 THEN E.BP_ID
END AS DISPOSAL_BP

So SAS is saying the D.BP_ID and E.BP_ID are different types of values. One is a character string and the other is a number.

So you need to check to figure out why BP_ID is not using the same type in those two datasets:

LEFT JOIN WRK.FINAL_BRACC_STAGE_02 AS D
 ON B.DISPOSAL_ACCNRSER = D.ACCNRSER

LEFT JOIN FIN.FINAL_SAP_ALL AS E
 ON B.DISPOSAL_ACCOUNT_NUM = E.ACCNRSER

From the name it sounds like those variables are identifiers, so they should be character (why would you ever want to calculate the mean of an identifier?). You should fix that in the earlier steps that created those two datasets.

But if all of the values can be represented exactly as integers then it might be easiest to just fix this query by converting the one that is character into a number but add INPUT() function call into your CASE clause.

So if the second one is the character variable then the CASE could become:

, CASE
WHEN C.ACNT_SOURCE_SYSTEM = 1 THEN D.BP_ID
WHEN C.ACNT_SOURCE_SYSTEM = 110 THEN input(E.BP_ID,32.)
END AS DISPOSAL_BP

topic Re: Result of WHEN clause 2 is not the same data type as the preceding results. in SAS Programming

Result of WHEN clause 2 is not the same data type as the preceding results.

Re: Result of WHEN clause 2 is not the same data type as the preceding results.

Re: Result of WHEN clause 2 is not the same data type as the preceding results.

Re: Result of WHEN clause 2 is not the same data type as the preceding results.