Hi,
in order to update a table with values from a different table I followed the description in...:
http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a002595787.htm
This is what I wrote:
proc sql;
delete from p_kontoAGG_rest2 as k
where isin in (select isin from p_isin_mult)
and kusa in (select distinct kusa from p_isin_gruppe_join) and c200 not in (select c200 from output);
update work.p_kontoAgg_rest2 as k
set k.rwa = (select summe_rwa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)
where k.isin=i.isin and k.kusa = i.kusa;
quit;
But I got the following error... What did I do wrong?
14 proc sql;
15 /* Entfernung der Datensätze, deren RWA auf eine andere Position aufgeschlagen wird */
16 delete from p_kontoAGG_rest2 as k
17 where isin in (select isin from p_isin_mult)
18 and kusa in (select distinct kusa from p_isin_gruppe_join) and c200 not in (select c200 from output);
NOTE: No rows were deleted from WORK.P_KONTOAGG_REST2.
19
20 update p_kontoAgg_rest2 as k
21 set k.rwa = (select summe_rwa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)
_
73
76
ERROR 73-322: Expecting an =.
ERROR 76-322: Syntax error, statement will be ignored.
Thx, S.
I think that it is actually complaining about the use of the alias prefix on the name of the variable to be updated.
Instead of K.RWA = ... just write RWA = ... Here is an example that generates the same error message.
212 proc sql ;
213 update a
214 set a.age=(select age from b where a.name = b.name)
-
73
76
ERROR 73-322: Expecting an =.
ERROR 76-322: Syntax error, statement will be ignored.
215 ;
216 quit;
The alias is not needed since you can only make changes to the variables in the table named in the UPDATE clause.
Maybe your error is from the UPDATE statement :
You need use sub-query to obtain the obs you need to be updated.
update work.p_kontoAgg_rest2 as k
set k.rwa = (select summe_rwa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)
where k.isin=(select isin from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa) and k.kusa =(select kusa from p_isin_gruppen as i where k.isin=i.isin and k.Kusa = i.Kusa)
;
quit;
Ksharp
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.