topic Re: Understanding keywords: merge, keep and by in SAS Programming

Understanding keywords: merge, keep and by

actuarial — Mon, 04 Sep 2023 12:06:48 GMT

Re: Understanding keywords: merge, keep and by

PaigeMiller — Sat, 02 Sep 2023 11:51:48 GMT

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.

Re: Understanding keywords: merge, keep and by

actuarial — Sat, 02 Sep 2023 11:53:42 GMT

Thank you so much for your help and support so far!

Here are some code that joins table together using file ref and provider code. But for Annual policies, the provider code is not unique within each file ref. So to put the tables together, I need to learn the merge command.

PROC SQL;
CREATE TABLE Together AS

/* SELECT */
SELECT
base.*, /*take everything in the base table. Note this is not the same as SELECT *, because that takes everything*/
PR.'PremiumRank'n,
wantTop5.'avgmarkettop5'n,
wantTop10.'avgmarkettop10'n,
wantAverage.'avg'n
FROM WORK.QUERY_FOR_MSM_OUTPUT_0001 base
LEFT JOIN PremiumRank as PR on base.file_ref=PR.file_ref and base.'Provider Code'n=PR.'Provider Code'n
LEFT JOIN wantTop5 as five on base.file_ref=five.file_ref and base.'Provider Code'n=five.'Provider Code'n
LEFT JOIN wantTop10 as ten on base.file_ref=ten.file_ref and base.'Provider Code'n=ten.'Provider Code'n
LEFT JOIN wantAverage as average on base.file_ref=average.file_ref and base.'Provider Code'n=average.'Provider Code'n;

run;

Re: Understanding keywords: merge, keep and by

actuarial — Sat, 02 Sep 2023 12:03:34 GMT

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

Re: Understanding keywords: merge, keep and by

actuarial — Sat, 02 Sep 2023 12:13:49 GMT

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

Re: Understanding keywords: merge, keep and by

actuarial — Sat, 02 Sep 2023 12:23:25 GMT

This does seem to work. But how to put everything together?

Re: Understanding keywords: merge, keep and by

actuarial — Sat, 02 Sep 2023 12:56:18 GMT

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;

Re: Understanding keywords: merge, keep and by

PaigeMiller — Sat, 02 Sep 2023 13:33:56 GMT

Combining the three data sets is a single merge in a data step.