Proc SQL issue

deleted_user — Wed, 26 Nov 2008 15:03:51 GMT

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

Re: Proc SQL issue

Doc_Duke — Wed, 26 Nov 2008 17:47:36 GMT

Check the documentation. SAS's implementation of the UPDATE statement does not support the FROM clause.

Re: Proc SQL issue

deleted_user — Wed, 26 Nov 2008 18:40:30 GMT

Thanks for your response, but I'm struggling with how it should be written then. I've tried so many things, but it still doesn't work. Without using the From clause, how would the program know what table to reference?

Re: Proc SQL issue

LinusH — Wed, 26 Nov 2008 21:47:35 GMT

Well, you can use in-line views in your set statement together with a where-clause. See my example on SASHELP.CLASS:

data classGrade;
input name $ grade $;
datalines;
Alfred A
Barbara C
Janet D
;
run;

data class;
set sashelp.class;
length eval $6;
run;

PROC SQL;
update class as a
set eval = (select case when grade = 'A' then 'GOOD' when 'D' then 'BAD' else 'MEDIUM' end
from classGrade as b
where a.name eq b.name)
where a.name in(select name from classGrade)
;
quit;

If your data is large, I think performance could be an issue with this SQL. To prevent long running updates, index your transaction table on the id column(s).

Another option is simply to recreate the table using a join to do your calculation.

/Linus

topic Proc SQL issue in SAS Programming

Proc SQL issue

Re: Proc SQL issue

Re: Proc SQL issue

Re: Proc SQL issue