SAS Procedures

Help using Base SAS procedures
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
 )
	;

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

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
  • 10011 views
  • 0 likes
  • 2 in conversation