Need to create flag for Top 5% members by Total Cost for every Employer group ID

[ashwini2]

I have about 4000 distinct Employer groups in my data set and i need a flag or something similar that gives me top 5% members by Total Cost for each employer Group ID. How do i do it in SAS?

[Reeza]

Summarize your data to find total cost and then use PROC RANK to get the top 5%.

---

@ashwini2 wrote:

I have about 4000 distinct Employer groups in my data set and i need a flag or something similar that gives me top 5% members by Total Cost for each employer Group ID. How do i do it in SAS?

---

[ashwini2]

Thank you. But how would i create a distinct flag for every Employer ID? The flag should be something like 1(If member falls in the top 5% range) and 0, if not.

Or if you know a better way to deal this, Please suggest.

[Reeza]

---

@ashwini2 wrote:

Thank you. But how would i create a distinct flag for every Employer ID? The flag should be something like 1(If member falls in the top 5% range) and 0, if not.

Or if you know a better way to deal this, Please suggest.

---

Please provide sample data and an example of expected output if you want example code. 

[SuryaKiran]

You can achieve this in several ways. Please show us the sample data you have and how you want it. 

[ashwini2]

Attaching sample data set. And flag is the variable i want to create. 1= For member who falls in top 5% by total cost bucket and 0= Not in the Top 5% bucket and i want to do this for every employer group.

[Reeza]

1. Use PROC MEANS to get the total

2. USE PROC RANK with GROUPS=20 to get 5 percentiles - if you have duplicates/ties double check PROC RANK options for handling ties.

3. Merge back with original data to create your flag.

---

@ashwini2 wrote:

Attaching sample data set. And flag is the variable i want to create. 1= For member who falls in top 5% by total cost bucket and 0= Not in the Top 5% bucket and i want to do this for every employer group.

---

[mkeintz]

You apparently want the top 5% within each employer_group_id, although your data display does not make that absolutely clear.

Edited addition:  by "top 5%" I presume you mean top 5% of patients, not patients accounting for top 5% of total expenditures, is that correct?  If it's the latter then this same code could be tweaked to do what you want.

Your data appear to be already sorted by descending total_cost within employer_group_id.  If so then this code would work:

data want (drop=_:);
  set have;
  by employer_group_id;
  _n+1;
  if first.employer_group_id then _n=1;

  if last.employer_group_id;
  _cutoff=ceil(.05*_n);
  flag=1;
  do _i=1 to _n;
    set have;
	if _i>_cutoff then flag=0;
    output;
  end;
run;

I can't test it because you have not provide data in a sas data step form.

The code reads and counts records (variable _n) one-record-at-a-time until the last records for a given employer_group_id.  At that point it calculates the 5% cutoff, i.e. smallest integer greater than or equal to .05*_N, then re-reads the same records and assign values based on their sequential position within the group vs the cutoff.