Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

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

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;

Example of my goal


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...