update with a proc sql

Question

It should be an easy one but I'm stuck

Proc sql; 

UPDATE dicofr 
SET    dicofr.period = correspondance.period 
FROM   dicofr 
INNER JOIN correspondance 
ON dicofr.name_fic = correspondance.name_fic; 

I was thinking my update would be done but I got this error instead.

271  proc sql;
272  update dicofr
273  set dicofr.period = correspondance.period
           -
           73
           76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

I've tried with a straight join with a select

proc sql;
SELECT * FROM dicofr INNER JOIN correspondance 
ON dicofr.nom_fic=correspondance.nom_fic;

The select is fine.

How come?

Is my SQL query not correct? I don't think so ...

Edited: It seems the update I want to do is not possible. Is there a way to do what I want with SAS language?

Answer 1

SAS doesn't support JOINs in an UPDATE statement, for some reason. You need to do it through a nested select.

proc sql;
update tableA A
set var=
  (select var 
  from tableB B 
  where B.id=A.id)
where exists (
  select 1 
  from tableB B
  where B.id=A.id);
quit;