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

[VALLY]

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

54
55 LEFT JOIN DTA.ACCLOC AS C ON B.DISPOSAL_ACCOUNT_NUM = C.ACNT_NUMBER
56
57 LEFT JOIN WRK.FINAL_BRACC_STAGE_02 AS D ON B.DISPOSAL_ACCNRSER = D.ACCNRSER
58
59 LEFT JOIN FIN.FINAL_SAP_ALL AS E ON B.DISPOSAL_ACCOUNT_NUM = E.ACCNRSER
60
61 WHERE A.OPENORCLOSED = "Open"
62 AND
63 B.DISPOSAL_ACCNRSER <>.
64 ;
NOTE: The "<>" operator is interpreted as "not equals".
NOTE: Data file DTA.ACCLOC.DATA is in a format that is native to another host, or the file encoding does not match the session
encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce
performance.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-09-10T11:02:37,612+02:00| _DISARM| WorkspaceServer| _DISARM| SAS|
_DISARM| | _DISARM| 2337736| _DISARM| 27815936| _DISARM| 13| _DISARM| 22| _DISARM| 0| _DISARM| 43486264| _DISARM| 0.010000|
_DISARM| 0.083033| _DISARM| 1946883757.529157| _DISARM| 1946883757.612190| _DISARM| 0.010000| _DISARM| | _ENDDISARM
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-09-10T11:02:37,612+02:00| _DISARM| WorkspaceServer| _DISARM| SAS|
_DISARM| | _DISARM| 212344| _DISARM| 27815936| _DISARM| 13| _DISARM| 22| _DISARM| 0| _DISARM| 43486264| _DISARM| 0.010000|
_DISARM| 0.055735| _DISARM| 1946883757.556856| _DISARM| 1946883757.612591| _DISARM| 0.010000| _DISARM| | _ENDDISARM
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-09-10T11:02:37,612+02:00| _DISARM| WorkspaceServer| _DISARM| SAS|
_DISARM| | _DISARM| 23657534| _DISARM| 27815936| _DISARM| 13| _DISARM| 22| _DISARM| 0| _DISARM| 43486264| _DISARM| 0.000000|
_DISARM| 0.022181| _DISARM| 1946883757.590749| _DISARM| 1946883757.612930| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-09-10T11:02:37,613+02:00| _DISARM| WorkspaceServer| _DISARM| SAS|
_DISARM| | _DISARM| 2337736| _DISARM| 27815936| _DISARM| 13| _DISARM| 22| _DISARM| 0| _DISARM| 43486264| _DISARM| 0.000000|
_DISARM| 0.022048| _DISARM| 1946883757.591209| _DISARM| 1946883757.613257| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-09-10T11:02:37,613+02:00| _DISARM| WorkspaceServer| _DISARM| SAS|
_DISARM| | _DISARM| 21849807| _DISARM| 27553792| _DISARM| 13| _DISARM| 22| _DISARM| 0| _DISARM| 43486264| _DISARM| 0.000000|
_DISARM| 0.002352| _DISARM| 1946883757.611246| _DISARM| 1946883757.613598| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
65 QUIT ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2021-09-10T11:02:37,614+02:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| |
_DISARM| 2136903680| _DISARM| 27553792| _DISARM| 13| _DISARM| 22| _DISARM| 0| _DISARM| 43486264| _DISARM| 0.010000| _DISARM|
0.098059| _DISARM| 1946883757.516096| _DISARM| 1946883757.614155| _DISARM| 0.010000| _DISARM| | _ENDDISARM
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 7232.15k
OS Memory 32288.00k
Timestamp 2021/09/10 11:02:37 AM
Step Count 53 Switch Count 0
Page Faults 0
Page Reclaims 95
Page Swaps 0
Voluntary Context Switches 18
Involuntary Context Switches 9
Block Input Operations 0
Block Output Operations 0

66
67 GOPTIONS NOACCESSIBLE;
68 %LET _CLIENTTASKLABEL=;
69 %LET _CLIENTPROCESSFLOWNAME=;
70 %LET _CLIENTPROJECTPATH=;
3 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

[Shmuel]

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.

[Kurt_Bremser]

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".

[Tom]

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