choose top 10 percent data based on one variable

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

choose top 10 percent data based on one variable

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);

 


Accepted Solutions
Solution
‎05-20-2016 03:35 PM
Super User
Posts: 10,508

Re: choose top 10 percent data based on one variable

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".

 

View solution in original post


All Replies
Super User
Posts: 10,508

Re: choose top 10 percent data based on one variable

[ Edited ]

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;

Super Contributor
Posts: 312

Re: choose top 10 percent data based on one variable

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.

Solution
‎05-20-2016 03:35 PM
Super User
Posts: 10,508

Re: choose top 10 percent data based on one variable

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".

 

Super Contributor
Posts: 312

Re: choose top 10 percent data based on one variable

Thank you so much!!!

 

Have a great weekend.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1309 views
  • 0 likes
  • 2 in conversation