topic Re: Update a table with values from a different table in SAS Procedures

Update a table with values from a different table

sfmeier — Mon, 20 Aug 2012 11:36:45 GMT

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.

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)

ERROR 73-322: Expecting an =.

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

Thx, S.

Re: Update a table with values from a different table

Tom — Mon, 20 Aug 2012 16:17:21 GMT

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)

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.

Re: Update a table with values from a different table

Ksharp — Tue, 21 Aug 2012 02:46:28 GMT

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