Help using Base SAS procedures

Proc SQL error

Reply
Occasional Contributor
Posts: 12

Proc SQL error

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 Star
Posts: 7,363

Proc SQL error

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.

Occasional Contributor
Posts: 12

Proc SQL error

apologies should have been a bit clearer before,

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

Regular Contributor
Posts: 229

Proc SQL error

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

Ask a Question
Discussion stats
  • 3 replies
  • 2833 views
  • 0 likes
  • 3 in conversation