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
Do any SAS gurus know if such a feat is possible in proc sql?
Regards,
Michael
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?
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.
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
Hi,
Can you post a bit of test data for each table, and what you expect out of it.
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.
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.