I'm posting this code that works in Microsoft SQL server, but not in Proc SQL. I can't figure out why. I am also posting how I changed it using Proc SQL and then the error I get. Can anyone help?
update t_captreatment
set id_num4 = (case when (a.id_type4 = 'REP_ID' and id_untrans4 = b.adprepid) then b.rep_id else id_num4 end),
id_num3 = (case when (a.id_type3 = 'REP_ID' and id_untrans3 = b.adprepid) then b.rep_id else id_num3 end),
id_num2 = (case when (a.id_type2 = 'REP_ID' and id_untrans2 = b.adprepid) then b.rep_id else id_num2 end),
id_num1 = (case when (a.id_type1 = 'REP_ID' and id_untrans1 = b.adprepid) then b.rep_id else id_num1 end)
from
t_captreatment a,
t_rep b
where (a.id_type4 = 'REP_ID' and a.id_untrans4 = b.adprepid)
or (a.id_type3 = 'REP_ID' and a.id_untrans3 = b.adprepid)
or (a.id_type2 = 'REP_ID' and a.id_untrans2 = b.adprepid)
or (a.id_type1 = 'REP_ID' and a.id_untrans1 = b.adprepid)
PROC SQL;
update t_captreatment
set id_num4 = (case when (a.id_type4 = 'REP_ID' and id_untrans4 = b.adprepid) then b.rep_id else id_num4 end),
id_num3 = (case when (a.id_type3 = 'REP_ID' and id_untrans3 = b.adprepid) then b.rep_id else id_num3 end),
id_num2 = (case when (a.id_type2 = 'REP_ID' and id_untrans2 = b.adprepid) then b.rep_id else id_num2 end),
id_num1 = (case when (a.id_type1 = 'REP_ID' and id_untrans1 = b.adprepid) then b.rep_id else id_num1 end)
from
t_captreatment a,
t_rep b
where (a.id_type4 = 'REP_ID' and a.id_untrans4 = b.adprepid)
or (a.id_type3 = 'REP_ID' and a.id_untrans3 = b.adprepid)
or (a.id_type2 = 'REP_ID' and a.id_untrans2 = b.adprepid)
or (a.id_type1 = 'REP_ID' and a.id_untrans1 = b.adprepid);
QUIT;
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code);
4 %LET _EGTASKLABEL=%NRBQUOTE(Code);
5 %LET _CLIENTPROJECTNAME=%NRBQUOTE();
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
11 %gaccessible;
12 PROC SQL;
13 update t_captreatment
14 set id_num4 = (case when (a.id_type4 = 'REP_ID' and id_untrans4 = b.adprepid) then b.rep_id else id_num4 end),
15 id_num3 = (case when (a.id_type3 = 'REP_ID' and id_untrans3 = b.adprepid) then b.rep_id else id_num3 end),
16 id_num2 = (case when (a.id_type2 = 'REP_ID' and id_untrans2 = b.adprepid) then b.rep_id else id_num2 end),
17 id_num1 = (case when (a.id_type1 = 'REP_ID' and id_untrans1 = b.adprepid) then b.rep_id else id_num1 end)
18 from
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, WHERE, ||.
ERROR 76-322: Syntax error, statement will be ignored.
19 t_captreatment a,
20 t_rep b
21 where (a.id_type4 = 'REP_ID' and a.id_untrans4 = b.adprepid)
22 or (a.id_type3 = 'REP_ID' and a.id_untrans3 = b.adprepid)
23 or (a.id_type2 = 'REP_ID' and a.id_untrans2 = b.adprepid)
24 or (a.id_type1 = 'REP_ID' and a.id_untrans1 = b.adprepid);
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.00 seconds
cpu time 0.00 seconds
26
27
28
29 %LET _CLIENTTASKLABEL=;
30 %LET _EGTASKLABEL=;
31 %LET _CLIENTPROJECTNAME=;
32 %LET _SASPROGRAMFILE=;
33
34 ;*';*";*/;quit;run;
35 ODS _ALL_ CLOSE;
36
37
38 QUIT; RUN;
39