I'm building a results table and need to do two subqueries to calculate some KPI's. When running the code without grouping by in the subqueries, it works. However, the KPI's aren't correct because I need them grouped by 'TRAMO_CAP_FINAL' - if not, I get the same figures for the different groups. Then I include a GROUP BY in the subqueries, but it doesn't work and get the abovementioned error (SAS ERROR: Subquery evaluated to more than one row).
Your help would be much appreciated.
PROC SQL;
CREATE TABLE WORK.OUTPUT_MC_SEGMENT AS
SELECT "MIDCORP" AS SEGMENT,
t1.TRAMO_CAP_FINAL,
(SUM(t1.GWP)/SUM(t1.CAP_FINAL))*1000 AS AVG_BOOK_RATE,
(SELECT (SUM(t1.GWP)/SUM(t1.CAP_FINAL))*1000 AS AVG_RATE_lowmid
FROM TABLA_FINAL_MC t1
WHERE t1.HG IN (1,2,3,4,5) AND t1.INFORCE_P=1
GROUP BY t1.TRAMO_CAP_FINAL),
(SELECT (SUM(t1.GWP)/SUM(t1.CAP_FINAL))*1000 AS AVG_RATE_high
FROM TABLA_FINAL_MC t1
WHERE t1.HG IN (6,7,8,9) AND t1.INFORCE_P=1
GROUP BY t1.TRAMO_CAP_FINAL)
FROM TABLA_FINAL_MC t1
WHERE t1.INFORCE_P=1
GROUP BY t1.TRAMO_CAP_FINAL;
QUIT;