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!
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)
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.
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)
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!
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.
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)
That is precisely what I was trying to do, thank you very much PGStats!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: