Average of top 5 cheapest

[actuarial]

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

[PaigeMiller]

For this to work, I think you need the use the DESCENDING option in PROC RANK.

/* UNTESTED CODE */
proc summary data=results nway;
    by file_ref;
    var premiumrank;
    output out=average mean=avgmarkettop5;
run;
data want;
    merge query_for_msm_output_0001 average(keep=file_ref avgmarkettop5);
    by file_ref;
run;

If you want tested code, you ahve to provide the data as working SAS data step code (examples and instructions), and not via attachments, and not using any other method.

[actuarial]

Thank you so much for your help and advice!

The script is:

%_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 descending;
   by File_Ref;
   var 'Premium'n;
   ranks PremiumRank;
run;

proc summary data=results nway;
    by file_ref;
    var premiumrank;
    output out=average mean=avgmarkettop5;
run;
data want;
    merge query_for_msm_output_0001 average(keep=file_ref avgmarkettop5);
    by file_ref;
run;

So I pasted your script, and obtained the "want" table

But the "average market top 5" appears to be too large. The cheapest 5 quotes are

14.3

14.67

14.96

15.3

15.45

which should give an average value of 14.936

[PaigeMiller · Accepted solution]

My mistake, try this

proc summary data=results(where=(premiumrank<=5)) nway;
    by file_ref;
    var premium;
    output out=average mean=avgmarkettop5;
run;