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
My mistake, try this
proc summary data=results(where=(premiumrank<=5)) nway;
by file_ref;
var premium;
output out=average mean=avgmarkettop5;
run;
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.
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
My mistake, try this
proc summary data=results(where=(premiumrank<=5)) nway;
by file_ref;
var premium;
output out=average mean=avgmarkettop5;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: