SAS ERROR: Subquery evaluated to more than one row - SQL query

Question

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;

Answer 1

Use conditional aggregation as follows:

SELECT "MIDCORP" AS SEGMENT,
          t1.TRAMO_CAP_FINAL,
          (SUM(t1.GWP)/SUM(t1.CAP_FINAL))*1000 AS AVG_BOOK_RATE,
          (SUM(case when t1.HG IN (1,2,3,4,5) AND t1.INFORCE_P=1 then t1.GWP end)/SUM(case when t1.HG IN (1,2,3,4,5) then t1.CAP_FINAL end))*1000 AS AVG_RATE_lowmid,
          (SUM(case when t1.HG IN (6,7,8,9) AND t1.INFORCE_P=1 then t1.GWP end)/SUM(case when t1.HG IN (6,7,8,9) then then t1.CAP_FINAL end))*1000 AS AVG_RATE_high
   FROM TABLA_FINAL_MC t1
   WHERE t1.INFORCE_P=1
   GROUP BY t1.TRAMO_CAP_FINAL;