PROC SUMMARY computes averages (and many other statistics, if requested) and stores the result in a SAS data set.
The MERGE command in the data step combines two different SAS data sets, side-by-side, so now the original data, and the averages computed by PROC SUMMARY are both in a single new data step. The BY statement causes this merge operation to be done file_ref by file_ref.
To get the WantTop5 and WantTop10 in the same data set, you MERGE the two data sets.
_TYPE_ is created by PROC SUMMARY, and I wouldn't worry about _TYPE_ at this point, it is irrelevant to this problem.
Is there a resource you can point me to, to get results like the following table, please?
Premium | Rank for Variable Premium | avgmarkettop5 | avgmarkettop10 | avg |
13.5 | 1 | 14.17 | 15.36 | 37.22259 |
13.8 | 2 | 14.17 | 15.36 | 37.22259 |
13.84 | 3 | 14.17 | 15.36 | 37.22259 |
14.71 | 4 | 14.17 | 15.36 | 37.22259 |
15 | 5 | 14.17 | 15.36 | 37.22259 |
Another problem: Is it possible to get the average market 6 to 10?
I tried:
data=PremiumRank(where=(6<=premiumrank<=10))
but the SAS did not understand?
Premium | Rank | Avg Market Top 5 | Avg Market Top 10 | Avg Market 6-10 | Avg Market |
10.26 | 1 | 12.876 | 15.9 | 18.924 | 31.73816667 |
11.4 | 2 | 12.876 | 15.9 | 18.924 | 31.73816667 |
12.71 | 3 | 12.876 | 15.9 | 18.924 | 31.73816667 |
13.35 | 4 | 12.876 | 15.9 | 18.924 | 31.73816667 |
16.66 | 5 | 12.876 | 15.9 | 18.924 | 31.73816667 |
I have the answer to my own question. Thank you for letting me figuring out how to code myself.
data work.QUERY_FOR_MSM_OUTPUT_0003 ;
infile datalines dsd dlm='|' truncover;
input File_Ref :$72. "Provider Code"N Premium ;
format File_Ref $char72. "Provider Code"N best4. ;
informat File_Ref $char72. "Provider Code"N best4. ;
datalines4;
Input_file_20230901T230451_208|1046|134.8
Input_file_20230901T230451_208|41|166.08
Input_file_20230901T230451_208|1008|175.26
Input_file_20230901T230451_208|1046|184.91
Input_file_20230901T230451_208|1005|186.25
Input_file_20230901T230451_208|1053|187.91
Input_file_20230901T230451_208|1024|195.57
Input_file_20230901T230451_208|87|196.98
Input_file_20230901T230451_208|1025|200.4
Input_file_20230901T230451_208|1010|208.4
Input_file_20230901T230451_208|1008|237.36
Input_file_20230901T230451_208|1005|245.25
Input_file_20230901T230451_208|1024|245.33
Input_file_20230901T230451_208|41|248.51
Input_file_20230901T230451_208|2|253.04
Input_file_20230901T230451_208|87|254.18
Input_file_20230901T230451_208|1059|257.75
Input_file_20230901T230451_208|1010|261.09
Input_file_20230901T230451_208|1046|272.67
Input_file_20230901T230451_208|1053|273.18
;;;;
/*create the premium ranking*/
proc rank data=WORK.QUERY_FOR_MSM_OUTPUT_0003 out=PremiumRank ties=low;
by File_Ref;
var 'Premium'n;
ranks PremiumRank;
run;
/*code for top 5*/
proc summary data=PremiumRank(where=(premiumrank<=5)) nway;
by file_ref;
var premium;
output out=average5 mean=avgmarkettop5;
run;
data wantTop5;
merge PremiumRank average5(keep=file_ref avgmarkettop5);
by file_ref;
run;
/*code for top 10*/
proc summary data=PremiumRank(where=(premiumrank<=10)) nway;
by file_ref;
var premium;
output out=average10 mean=avgmarkettop10;
run;
data wantTop10;
merge wantTop5 average10(keep=file_ref avgmarkettop10);
by file_ref;
run;
/*code for top 6 to 10*/
proc summary data=PremiumRank(where=(6<=premiumrank<=10)) nway;
by file_ref;
var premium;
output out=average6to10 mean=avgmarket6TO10;
run;
data wantTop6to10;
merge wantTop10 average6to10(keep=file_ref avgmarket6TO10);
by file_ref;
run;
/*code for overall average*/
proc summary data=PremiumRank nway;
by file_ref;
var premium;
output out=average mean=avg;
run;
data wantAVG;
merge wantTop6to10 average(keep=file_ref avg);
by file_ref;
run;
Combining the three data sets is a single merge in a data step.
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.