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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.