topic Re: How to get the highest 95% of a group? in SAS Programming

How to get the highest 95% of a group?

Shradha1 — Fri, 22 Jan 2021 15:04:24 GMT

I have a list of numbers belonging to some groups. Dataset is sorted in descending order:
Group A 10
Group A 5
Group A 3
Group A 2
Group A 1
Group B 15
Group B 7
Group B 2
How do I pick those observations which belong within the top 95% or more within a particular group? Like the total of group A is 21, so the first four observations in group A will contribute to the top 95% and the first 2 observations of Group B
I want to create a flag against observations which satisfy this criteria . So in this case there will be a flag for first 4 obs of Group A and first 2 of Group B.

Re: How to get the highest 95% of a group?

ballardw — Fri, 22 Jan 2021 15:43:27 GMT

First, you should show exactly what the desired output looks like.

Second, 95% of what? Percentages involve numerators and denominators. You show 5 values in our A group. That means each value is 20% of the number of values. Or is the numeric value a count of some sort? So do you mean 95% of the total count?

If your data is supposed to be counts how did you make them? That could simplify you issue.

Consider the results here that adds a cumulative percent when counting.

proc sort data=sashelp.class
   out=work.class;
   by sex;
run;
proc freq data=work.class order=freq;
   by sex;
   ods output onewayfreqs=work.count;
   tables age ;
run;

you need to decide and describe what the result looks like when there is not a "nice" 95% boundary.

It is very likely that the next to last in a sorted group is considerably less than 95 and jumps from maybe 80% to 100.

And what about ties of the value?

Re: How to get the highest 95% of a group?

PaigeMiller — Fri, 22 Jan 2021 15:46:17 GMT

In your earlier thread, code was provided to compute the cumulative percent by group. So any observation where the cumulative percent is <=95% would be flagged.

Re: How to get the highest 95% of a group?

PhilC — Fri, 22 Jan 2021 15:49:31 GMT

So I used SAShelp.CARS. Proc means can calculate percentiles for you. You can then merge those percentiles back with the original dataset selecting records less than or greater than the percentile for the group.

Here I'm finding the MSRPs less than the 75th percentile for each Make.

PROC MEANS DATA=SASHELP.CARS
	NOPRINT 	CHARTYPE	QMETHOD=OS NONOBS Q3	;
	VAR MSRP;
	BY Make;

  OUTPUT 	OUT=Percentiles
	  Q3()=
  	/ AUTONAME AUTOLABEL INHERIT
	;
RUN;

PROC SQL;
   CREATE TABLE wantish AS 
   SELECT CARS.Make, 
          CARS.Model, 
          CARS.MSRP
      FROM SAShelp.CARS 
             INNER JOIN 
           Percentiles p 
        ON (CARS.Make = p.Make)
      WHERE CARS.MSRP < p.MSRP_Q3;
QUIT;

Re: How to get the highest 95% of a group?

PGStats — Fri, 22 Jan 2021 15:51:35 GMT

With a data step:

data want;
do until(last.group);
    set have; by group;
    totValue = sum(totValue, value);
    end;
do until(last.group);
    set have; by group;
    cumValue = sum(cumValue, value);
    flag = (totValue - cumValue) >= 0.05 * totValue;
    output;
    end;
drop totValue cumValue;
run;