Proc SQL error

Wickywick — Wed, 14 Dec 2011 04:51:44 GMT

hi all, I'm trying to get the below code to run but keep getting the same error - any suggestions on what im doing wrong?

regards

code :

proc sql;

create table mrg_tst as

select AW.*

,AR.AR_ID

,AR.AR_LOCAL_ID

from All_WatchList AW

left join

API5AFS.AR_TRANSLATION AR

on AW.ACCT_NO=put(AR.AR_LOCAL_ID, $12.)

where AW.ACCT_NO=put(AR.AR_LOCAL_ID, $12.)

;

quit;

Error Log -

;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code);
4 %LET _EGTASKLABEL=%NRBQUOTE(Code);
5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(G:\Portfolio Management\Small Business Collections\SAS\Daily ESBC Account Refresh.egp);
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
9 OPTIONS DEV=ACTIVEX;
10 FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
11 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")
11 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
11 ! ;
12
13 %gaccessible;
14 proc sql;
15 create table mrg_tst as
16 select AW.*
17 ,AR.AR_ID
18 ,AR.AR_LOCAL_ID
19 from All_WatchList AW
20 left join
21 API5AFS.AR_TRANSLATION AR
22 on AW.ACCT_NO=put(AR.AR_LOCAL_ID, $12.)
23 where AW.ACCT_NO=put(AR.AR_LOCAL_ID, $12.)
24 ;
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
25 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
26
27 %LET _CLIENTTASKLABEL=;
28 %LET _EGTASKLABEL=;
29 %LET _CLIENTPROJECTNAME=;
30 %LET _SASPROGRAMFILE=;
31
32 ;*';*";*/;quit;run;
33 ODS _ALL_ CLOSE;
34
35
36 QUIT; RUN;

Proc SQL error

art297 — Wed, 14 Dec 2011 05:47:39 GMT

From your log I can't tell what the initial warnings are about but, regarding the proc sql code, you have two statements:

22 on AW.ACCT_NO=put(AR.AR_LOCAL_ID, $12.)

23 where AW.ACCT_NO=put(AR.AR_LOCAL_ID, $12.)

If aw.acct_no and ar.ar_local_id are both numbers, why are you converting the latter one to be character? They must both be of the same type.

Proc SQL error

Wickywick — Wed, 14 Dec 2011 05:50:28 GMT

apologies should have been a bit clearer before,

ar.ar_local_id is character and aw.acct_no is numeric.

Proc SQL error

R_Win — Wed, 14 Dec 2011 06:07:47 GMT

proc sql; create table mrg_tst as select AW.* ,AR.AR_ID ,AR.AR_LOCAL_ID from All_WatchList AW left join API5AFS.AR_TRANSLATION AR on AW.ACCT_NO=input(AR.AR_LOCAL_ID, 12.) where AW.ACCT_NO=input(AR.AR_LOCAL_ID,12.) ;quit; Try this

topic Proc SQL error in SAS Procedures

Proc SQL error

Proc SQL error

Proc SQL error

Proc SQL error