DATA Step, Macro, Functions and more

question on update function in proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

question on update function in proc sql

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.


Accepted Solutions
Solution
‎02-11-2013 10:04 PM
Super User
Super User
Posts: 7,050

Re: question on update function in proc sql

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 82

Re: question on update function in proc sql

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.

Solution
‎02-11-2013 10:04 PM
Super User
Super User
Posts: 7,050

Re: question on update function in proc sql

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.

Frequent Contributor
Posts: 82

Re: question on update function in proc sql

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.

Super User
Super User
Posts: 7,050

Re: question on update function in proc sql

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 202 views
  • 3 likes
  • 2 in conversation