BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
Doc_Duke
Rhodochrosite | Level 12
Check the documentation. SAS's implementation of the UPDATE statement does not support the FROM clause.
deleted_user
Not applicable
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?
LinusH
Tourmaline | Level 20
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
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 722 views
  • 0 likes
  • 3 in conversation