topic Re: choose top 10 percent data based on one variable in SAS Programming

choose top 10 percent data based on one variable

fengyuwuzu — Fri, 20 May 2016 15:02:22 GMT

I want to select out the top 10% data based on several variables (that means several sub sets, each bsed on one variable). I have an idea like the code below.

Is this the best way to do it? Or is there a better way to do it?

Thanks in advance!

/*first define a macro selecting first 10% data */

%macro top10pct(lib, dataset, var);
proc sql noprint;
select max(ceil(0.1*nlobs)) into :N_top10pct
from &lib..&dataset
;
quit;

data &lib..&var;
set &lib..&dataset;
if _n_ <= &N_top10pct;
run;
%mend top10pct;

/* top 10 % of var1 */
proc sort data=mylib.mydata; 
by var1;
run;
%top10pct(mylib, mydata, var1);

/* top 10 % of var2 */
proc sort data=mylib.mydata; 
by var2;
run;
%top10pct(mylib, mydata, var2);

/* top 10 % of var3 */
proc sort data=mylib.mydata; 
by var3;
run;
%top10pct(mylib, mydata, var3);

Re: choose top 10 percent data based on one variable

ballardw — Fri, 20 May 2016 16:49:58 GMT

What will you be doing with those multiple subsets? Often it is a better idea to add flag variables and selectively process than to keep track of multiple data sets. And if the base data is "large" you can have performance issues with many large datasets floating around.

Also your current approach is selecting the smallest values for your variable. Which sounds funny to call it the Top 10 percent.

Or did you mean to have your sort

By descending Var1;

Re: choose top 10 percent data based on one variable

fengyuwuzu — Fri, 20 May 2016 17:40:45 GMT

thank you. yes, I forgot to sort as "By descending Var1;" .

My base data has 76790 rows, so each 10% sub set will have 7679 rows.

Yes, I can think of using flag variables to indicate the top10 of each variables, just add a number of new flag variables. This is better than managing multiple data files.

Re: choose top 10 percent data based on one variable

ballardw — Fri, 20 May 2016 18:09:54 GMT

If the values are numeric here is a way to add a flag variable for those values at or above the 90th percentile

proc summary data=sashelp.cars;
   var  MSRP  Invoice horsepower;
   output out= carsum(drop= _:) p90=/autoname;
run;

Proc sql;
   create table want as
   select a.*, (a.Msrp ge b.Msrp_P90) as MSRP_flag,
     (a.Invoice ge b.Invoice_P90) as Invoice_flag,
     (a.horsepower ge b.horsepower_P90) as horsepower_flag
   from SASHELP.Cars as a,  Carsum as b;
quit;

Since Percentile may be an odd construct when dealing with character values something else may be needed. Consider whether you really want "ABC" to be greater than "ABBBQ".

Re: choose top 10 percent data based on one variable

fengyuwuzu — Fri, 20 May 2016 19:35:51 GMT

Thank you so much!!!

Have a great weekend.

Re: choose top 10 percent data based on one variable

raqthesolid — Tue, 28 Nov 2017 10:46:37 GMT

Hello guys,

how to transform this code if i need top30 percentile and bottom30 percentile at the same time.
thanks in advance