In the attached file, each File_Ref represent a risk profile. Then the premium of providers is listed. Using the code below, I was able to rank the premiums grouped by each file reference %_eg_conditional_dropds(WORK.QUERY_FOR_MSM_OUTPUT_0001);
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_MSM_OUTPUT_0001 AS
SELECT t1.File_Ref,
t1.'Provider Code'n,
t1.Provider,
t1.'Single Trip Premium'n,
t1.'Annual Premium'n,
t1.'Medical Value'n,
t1.'Medical Excess'n,
t1.'Baggage Value'n,
t1.'Baggage Excess'n,
t1.'Cancellation Value'n,
t1.'Cancellation Excess'n,
t1.'Defaqto Rating'n,
t1.'Personal Money Value'n,
t1.'Personal Money Excess'n,
t1.Cover,
t1.'New ST Premium'n,
t1.'New AMT Premium'n,
/* Premium */
(t1.'New ST Premium'n+t1.'New AMT Premium'n) AS Premium
FROM WORK.QUERY_FOR_MSM_OUTPUT t1
ORDER BY t1.File_Ref,
Premium;
QUIT;
proc rank data=WORK.QUERY_FOR_MSM_OUTPUT_0001 out=results ties=low ;
by File_Ref;
var 'Premium'n;
ranks PremiumRank;
run; Now, I would like to get the average premium of the top 5. I.e., the average premium of the top 5 cheapest quotes. In Excel, the average if function gave the desired answer. Then all other values in the columns are populated with the same average value as above
... View more