BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sfmeier
Obsidian | Level 7

Dear experts,

I am trying to update a table with values from a different table by using a subquery.

      

      proc sql;

      /* Update durchführen */

      update p_ctryexp as c

            set RT_Kusa = (select RT_Kusa from tempis),

                  RT_name = (select RT_Name from tempis),

                  RT_IntRating = (select RT_IntRating from tempis),

                  Sektor = (select Sektor from tempis)

            where substr(c.KontoID, 1, max(1,length(c.KontoID)-4)) = (select KontoID

                                                                                                                                      from tempis as t

                                                                                                                                      where substr(c.KontoID, 1, max(1,length(c.KontoID)-4)) = t.KontoID);

   quit;

Now, the update fails because the subquery evaluates to more that one row:

ERROR: Subquery evaluated to more than one row.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

87         quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.18 seconds

I cannot follow that, because the way I understand it, the subquery is executed on each row. However for the two rows in tempis, there is exactly onw corresponding row in p_ctryexp. The inner join of the two tables on the where-condition returns two rows. What did I do wrong?

Thx, Stefan

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

You probably need where on each subquery in the set statement.

Data never sleeps

View solution in original post

1 REPLY 1
LinusH
Tourmaline | Level 20

You probably need where on each subquery in the set statement.

Data never sleeps

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
  • 1 reply
  • 711 views
  • 0 likes
  • 2 in conversation