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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.