Help using Base SAS procedures

Updating Values returned from a join only

Reply
Occasional Contributor
Posts: 9

Updating Values returned from a join only

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

Occasional Contributor
Posts: 9

Re: Updating Values returned from a join only

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

Regards,

Michael

Super User
Posts: 17,868

Re: Updating Values returned from a join only

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?

Occasional Contributor
Posts: 9

Re: Updating Values returned from a join only

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.

Super User
Posts: 17,868

Re: Updating Values returned from a join only

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

Super User
Super User
Posts: 7,407

Re: Updating Values returned from a join only

Hi,

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

Ask a Question
Discussion stats
  • 5 replies
  • 156 views
  • 0 likes
  • 3 in conversation