DATA Step, Macro, Functions and more

Trimming the top and bottom 5% of a dataset

Accepted Solution Solved
Reply
Regular Contributor
Posts: 223
Accepted Solution

Trimming the top and bottom 5% of a dataset

From another similar question on these boards I found the following solution:

 

%let trim=0.05;
data trimmed;
set have
Nobs=NN;
if &trim*NN < _N_ <= (1-&trim)*NN
then output;
run;

1)  Do you concur with this way of trimming the tails?  (I'm not exactly sure what's going on with this.  Just want it to work.)

 

2)  Data needs to be sorted by the relevant variable we want to trim, yes?

 

Thanks!

 

Nicholas Kormanik

 

 


Accepted Solutions
Solution
‎09-02-2016 05:17 PM
Super User
Posts: 10,018

Re: Trimming the top and bottom 5% of a dataset

Posted in reply to NicholasKormanik
Opps. should use 


proc rank data=sashelp.cars groups=100 out=temp ;
 var invoice;
 ranks rank;
run;
data want;
 set temp;
 if 4 < rank < 94 ;
run;


Due to the fact that rank is 0-99 .


View solution in original post


All Replies
Super User
Posts: 19,768

Re: Trimming the top and bottom 5% of a dataset

Posted in reply to NicholasKormanik

Depends. Rules of 5% is vague, and often confused with percentiles. 

So define your rules first. 

Regular Contributor
Posts: 223

Re: Trimming the top and bottom 5% of a dataset

The approach I'm presently using is the manual, long-about way.

 

First running Proc Univariate I am obtaining the _P5_ and _P95_ actual numbers (say, 30 and 156).

 

I then plug these numbers into the following code.

 

data tails_trimmed_5_percent;
set have;
where 30 <= N <= 156;
run;

I have many such databases.  And I was thinking there might be a more direct general approach.

 

Such as, just imagining, mind you....

 

where (lower_tail_5_percent) <= N <= (upper_tail_5_percent);

 

But, appears life ain't that easy.

 

 

Super User
Posts: 10,018

Re: Trimming the top and bottom 5% of a dataset

Posted in reply to NicholasKormanik

A fast way to get it is using proc rank:

proc rank data=sashelp.cars groups=100 out=temp;
 var invoice;
 ranks rank;
run;
data want;
 set temp;
 if 5 < rank < 95 ;
run;

Another way is using IML, you want IML code ?

Regular Contributor
Posts: 223

Re: Trimming the top and bottom 5% of a dataset

Yes, please.  IML code, too.

 

Thanks a million!

 

Super User
Posts: 10,018

Re: Trimming the top and bottom 5% of a dataset

Posted in reply to NicholasKormanik
Here is . I set the value greater than 95th or less than 5th be missing .


data have;
 do i=1 to 100;
  a=ceil(ranuni(1)*100);
  b=ceil(ranuni(2)*100);
  output;
 end;
 drop i;
run;


%let low=0.05 ;
%let high=0.95 ;

proc iml;
use have;
read all var _num_ into x[c=vname];
close have;
call qntl(q,x,{&low ,&high});

do i=1 to ncol(x);
 x[loc(x[,i]q[2,i]),i]=.;
end;

create want from x[c=vname];
append from x;
close want;

quit;
Solution
‎09-02-2016 05:17 PM
Super User
Posts: 10,018

Re: Trimming the top and bottom 5% of a dataset

Posted in reply to NicholasKormanik
Opps. should use 


proc rank data=sashelp.cars groups=100 out=temp ;
 var invoice;
 ranks rank;
run;
data want;
 set temp;
 if 4 < rank < 94 ;
run;


Due to the fact that rank is 0-99 .


☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 507 views
  • 3 likes
  • 3 in conversation