BookmarkSubscribeRSS Feed
micksom
Calcite | Level 5

Hi All,

I am trying to run the following piece of code. Basically, I am trying to update the selected values from the where statement with the internal_product value returned from the join.

However, I am getting the error Subquery evaluated to more than one row. I should probably add that each product is linked to an account number in another column. Do I have to do some sort of grouping?

I was wondering if anyone could help restructure my code, or explain what is going on that is incorrect.

Proc Sql;

update Test

Set Product = (select b.product from

Table1 (where = (type = 1 and month = October))

inner join Table2 on

a.criteria1 = b.criteria1 and

a.criteria2 = b.criteria2

)

;

quit

5 REPLIES 5
micksom
Calcite | Level 5

Do any SAS gurus know if such a feat is possible in proc sql?

Regards,

Michael

Reeza
Super User

micksom wrote:

I should probably add that each product is linked to an account number in another column.

Have you tried joining on account number as well?

micksom
Calcite | Level 5

I don;t actually want to join on account number. The account number in table2 is different and not really linked to table1. I just really want to return the first value it finds based on criteria1 and criteria2.

Reeza
Super User

SQL doesn't really acknowledge order, so unless you have a way to identify the "first" record I'm not sure.

You could take the max or min if you don't actually care about order. The key is to get a query that returns only one value.

You'd also need quotes around October, I'm assuming it's simply a typo here.

Proc Sql;

update Test

Set Product = (select max(b.product) from

Table1 (where = (type = 1 and month = "October"))

inner join Table2 on

a.criteria1 = b.criteria1 and

a.criteria2 = b.criteria2

)

;

quit

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Can you post a bit of test data for each table, and what you expect out of it.

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
  • 5 replies
  • 732 views
  • 0 likes
  • 3 in conversation