topic Subquery evaluated to more than one row - Error in SAS Enterprise Guide

Subquery evaluated to more than one row - Error

S0MBR0 — Mon, 28 Jan 2019 15:38:22 GMT

Hello,

So I just recently began using SAS/SQL and have been working on creating a query that will give year to year numbers in adjacent columns. I've tried a few different ways but the most direct way to do so seemed to be writing subqueries that would isolate the data by year. I wrote a fairly simple query but I keep getting the "Error: Subquery evaluated to more than one row." message. Below is a the query I wrote, any help would be greatly appreciated.

proc sql;

CREATE TABLE DASHBOARD_2 AS

SELECT
	wk AS Week,
	SUM(rev_usd) = 
		(SELECT rev_usd
		FROM lib.TRANSACTIONS
		WHERE yr = 2019
			AND type = 'WORK'
			AND charge <> 'FEES'
			AND part <> 'FULL')
				AS CY_Rev, 
	SUM(rev_usd) =
		(SELECT rev_usd
		FROM lib.TRANSACTIONS
		WHERE yr = 2018
			AND type = 'WORK'
			AND charge <> 'FEES'
			AND part <> 'FULL')
				AS PY_Rev,
	SUM(cxl) =
		(SELECT cxl
		FROM lib.TRANSACTIONS
		WHERE yr = 2019
			AND type = 'WORK'
			AND charge <> 'FEES'
			AND part <> 'FULL')
				AS CY_Cancel, 
	SUM(cxl) =
		(SELECT cxl
		FROM lib.TRANSACTIONS
		WHERE yr = 2018
			AND type = 'WORK'
			AND charge <> 'FEES'
			AND part<> 'FULL')
				AS PY_Cancel
FROM lib.TRANSACTIONS
WHERE yr = 2018 OR 2019
	AND type = 'WORK'
	AND charge <> 'FEES'
	AND part <> 'FULL'
GROUP BY Week
ORDER BY Week DESC;


quit;

Thanks!

Re: Subquery evaluated to more than one row - Error

RW9 — Mon, 28 Jan 2019 15:56:39 GMT

What would you like us to say? The error is quite clear, one of the subqueries returns more than one record. As we can't see your data, or operation, there is nothing we can do except repeat the error to you?

Also, you will find it easier, if you want to use SAS, to learn Base SAS programming and use the functions, datasteps, and procedures used to manipulate data. In most scenarios it will be far quicker, easier to program and more maintainable. Sums can be done in proc means, summary, report etc.

Re: Subquery evaluated to more than one row - Error

ballardw — Mon, 28 Jan 2019 18:05:11 GMT

You also have a logic error in this statement:

WHERE yr = 2018 OR 2019

I suspect you intended to shortcut

where yr=2018 or yr=2019 but that is not what your code would do.

The comparison you wrote tests for yr=2018 OR if the value following or is true. SAS treats 2019 as "true" so the last where would select all records.

What you likely wanted to use is

where yr in (2018, 2019)

Re: Subquery evaluated to more than one row - Error

RW9 — Mon, 28 Jan 2019 18:07:46 GMT

Two things to add, re-reading your post. This syntax:

SUM(rev_usd) = 
		(SELECT rev_usd

Does not look correct syntax at all, you do not put sum()=(subquery). Possibly you have taken that from a specific database?

Secondly, the where clause after the group by most likely does not do what you think it does. Group by should use having clause.

Re: Subquery evaluated to more than one row - Error

S0MBR0 — Mon, 28 Jan 2019 18:34:31 GMT

Thank you ballardw, this is something I did not notice. I revised this as well as a bit of the subquery syntax and I've gotten it to work. I really appreciate the help!

Re: Subquery evaluated to more than one row - Error

PGStats — Mon, 28 Jan 2019 19:05:17 GMT

It looks like you might be trying to do:

proc sql;
CREATE TABLE DASHBOARD_2 AS
SELECT
	wk AS Week,
	SUM( case when yr = 2019 then rev_usd else . end) AS CY_Rev,
 	SUM( case when yr = 2018 then rev_usd else . end) AS PY_Rev, 
 	SUM( case when yr = 2019 then cxl else . end) AS CY_Cancel,
  	SUM( case when yr = 2018 then cxl else . end) AS PY_Cancel
FROM lib.TRANSACTIONS
WHERE yr in (2018, 2019)
	AND type = 'WORK'
	AND charge <> 'FEES'
	AND part <> 'FULL'
GROUP BY wk
ORDER BY Week DESC;
quit;

(untested)

Re: Subquery evaluated to more than one row - Error

S0MBR0 — Mon, 28 Jan 2019 20:44:11 GMT

That is precisely what I was trying to do, thank you very much PGStats!