BookmarkSubscribeRSS Feed
jbull
Calcite | Level 5

Hello New to SAS and new to the forum  I am just wondering if There is a way to update a table using a inner join.

 

 

Proc SQL;
	update AllDataSimple
	set AllDataSimple.gasprice = forward_gasprices.price
	from AllDataSimple
	inner join work.forward_gasprices
	on forward_gasprices.DeliveryDate = AllDataSimple.DeliveryDate
	where forward_gasprices.DeliveryDate > today()-1
	;
QUIT

SAS only highlights UPDATE  and SET and I get an error:

Expecting an =.  (affected code set AllDataSimple.gasprice = forward_gasprices.price)

 

When I search for a solution the error only came up when there was a syntax error and I am sorry if that is the case but it really seems to me like SAS just doesn't like the from or inner join and the set.

 

 

1 REPLY 1
Tom
Super User Tom
Super User

The SET keyword has a variable name and a value.

You can generate the value using a correlated subquery, just make sure it returns only one observation.

 

Or are you trying to tell UPDATE which observations to change?  For that you use a WHERE condition.  Again if the test to identify which observations to update involves another dataset then use a subquery, perhaps using EXISTS (). 

 

Perhaps something like this?

update AllDataSimple
  set AllDataSimple.gasprice = 
 ( select forward_gasprices.price 
   from AllDataSimple 
   inner join work.forward_gasprices
     on forward_gasprices.DeliveryDate = AllDataSimple.DeliveryDate
     and forward_gasprices.DeliveryDate > today()-1
 )

where exists 
 ( select forward_gasprices.price 
   from AllDataSimple 
   inner join work.forward_gasprices
     on forward_gasprices.DeliveryDate = AllDataSimple.DeliveryDate
     and forward_gasprices.DeliveryDate > today()-1
 )
	;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 10158 views
  • 0 likes
  • 2 in conversation