topic Re: question on update function in proc sql in SAS Programming

question on update function in proc sql

Xamius32 — Tue, 12 Feb 2013 02:37:59 GMT

Lets say I want to update table A based on a column in table B. Table B will have a list of games (like A, B, C, D) and if those games are in table A, I want to decrease a certain value. if I do

proc sql ;

update A set value=value-1

where game in (select game from B)

;

quit;

if dataset B has a game listed more than once, will the value decrease more than once? IE if its listed twice, the value in dataset A will decrease by 2.

Re: question on update function in proc sql

Xamius32 — Tue, 12 Feb 2013 02:42:06 GMT

and one more question, if I wanted to do the same thing, but using simply another column in table A, is that possible? When I try it by changing B to A, it says sql cant use the table it is updating to update.

Re: question on update function in proc sql

Tom — Tue, 12 Feb 2013 02:57:33 GMT

No. It will process each row in A and decide whether that GAME exists in B, if it does it will execute the update.

Re: question on update function in proc sql

Tom — Tue, 12 Feb 2013 03:04:06 GMT

You could make it execute by adding the UPDATE_POLICY=NONE but why? If you leave it coded the way it is the update will happen for every row as every value of GAME in dataset A is by definition in dataset A.

Re: question on update function in proc sql

Xamius32 — Tue, 12 Feb 2013 04:01:47 GMT

I realize by you saying that I am looking at this in the wrong way in terms of my goals. Anyways, thx for the help.