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!
... View more