BookmarkSubscribeRSS Feed
sfmeier
Obsidian | Level 7

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.

2 REPLIES 2
Tom
Super User Tom
Super User

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.

Ksharp
Super User

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2066 views
  • 3 likes
  • 3 in conversation