CODEWITHSUNDEEP
sqlsas
Andy K
Andy K

Reputation: 5075

update with a proc sql

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?

Upvotes: 6

Views: 32385

Answers (2)

Joe
Joe

Reputation: 63434

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;

Upvotes: 12

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

Yer you are taking a wrong approach. Try somthing like this:-

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

Upvotes: 0

Related Questions