Similar to moving percentile question

Accepted Solution Solved
Reply
Super Contributor
Posts: 402
Accepted Solution

Similar to moving percentile question

Hi Everyone,

I want to do a process similar to finding moving percentile.

However, for each record, I want to look back 500 records (excluding the current record) and extract the value of the 475th largest record (it is not the same as 95 percentile in SAS).

I prefer this way to percentile since it is comparable with other software I use and I can control the value better.

So for the data below, for the first record, the value-475 should be 476.

Thank you for your help.

HHC

data have;

do value=1 to 1000;output; end;

;run;


Accepted Solutions
Solution
‎12-14-2014 05:46 AM
Super User
Posts: 9,878

Re: Similar to moving percentile question

You mean look forward ?

data have;
     do value=1 to 1000;
           output;
     end;
     ;
run;

data want;
 set have nobs=nobs;
 array x{99999} _temporary_;
 do i=_n_+1 to min(nobs,_n_+499);
  set have(rename=(value=v)) point=i;
  x{i}=v;
 end;
 _25th=largest(25,of x{*});
 call missing(of x{*});
 drop i v;
run;

Xia Keshan

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: Similar to moving percentile question

I think you mean 25th largest?

data have;

     do value=1 to 1000;

           output;

     end;

     ;

run;

data want;

     array t(0:499) _temporary_;

     set have;

     t(mod(_n_,500))=value;

     if _n_>499 then

           _25th=largest(25,of t(*));

run;

Haikuo

Super Contributor
Posts: 402

Re: Similar to moving percentile question

Hi,

It is almost what I want.

However, the first record value for _25th should be 476.

file want should look like:

value     _25th

1          476

2          477

...

HHC

Respected Advisor
Posts: 3,156

Re: Similar to moving percentile question

This?

data want;

array t(0:499) _temporary_;

set have;

t(mod(_n_,500))=value;

if _n_>499 then do;

_25th=largest(25,of t(*));

n+1;

output;

end;

drop value;

rename n=value;

run;

Super Contributor
Posts: 402

Re: Similar to moving percentile question

Yes it is the what I want. But I want to keep the original 1000 records. Right now the last 499 records are gone.

If I have to do DO LOOP through this code, each time, the data is reduced by 499.

HHC

Solution
‎12-14-2014 05:46 AM
Super User
Posts: 9,878

Re: Similar to moving percentile question

You mean look forward ?

data have;
     do value=1 to 1000;
           output;
     end;
     ;
run;

data want;
 set have nobs=nobs;
 array x{99999} _temporary_;
 do i=_n_+1 to min(nobs,_n_+499);
  set have(rename=(value=v)) point=i;
  x{i}=v;
 end;
 _25th=largest(25,of x{*});
 call missing(of x{*});
 drop i v;
run;

Xia Keshan

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 247 views
  • 6 likes
  • 3 in conversation