Update using inner join

jbull — Wed, 24 Jul 2019 16:06:14 GMT

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.

Re: Update using inner join

Tom — Wed, 24 Jul 2019 16:15:51 GMT

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
 )
	;

topic Re: Update using inner join in SAS Procedures

Update using inner join

Re: Update using inner join