BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
ballardw
Super User

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;

fengyuwuzu
Pyrite | Level 9

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.

ballardw
Super User

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

 

fengyuwuzu
Pyrite | Level 9

Thank you so much!!!

 

Have a great weekend.

raqthesolid
Quartz | Level 8

Hello guys,

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 12154 views
  • 1 like
  • 3 in conversation