Update lag criteria for ranking Rate of change

Reply
Super Contributor
Posts: 406

Update lag criteria for ranking Rate of change

[ Edited ]

The idea is that, for each record, using the lagstep value of this current record to calculate the rate of change (ROC) for it and for the prior 9 record  (total 10 rate of change).

Then rank these 10 ROC and find whether the current record belong to 90,80,70,60 or below 60 percentile.

 

This code below is appropriate for record 16 where lagstep=5.
As you see I use Lag5 to calculate ROC.
The ROC_rank for this recrod 16 of 0.1 should be included in final file.

 

If I have to work on record 17, I will have to change the lag step to 2
If I have to work on record 18, I will have to change the lag step to 4

 

That lag is the only place the code need to be changed.

 

(in the real problem the rank is based on prior 1000 records)

 

Could you please help me with that/

 

Many thanks,

 

HHC

 

data have;
input day value lagstep;
datalines;
1 5 2
2 2 1
3 4 2
4 4 5
5 5 1
6 2 5
7 7 0
8 1 2
9 100 3
10 1 5
11 5 9
12 6 2
13 8 3
14 10 2
15 5 1
16 10 5
17 17 2
18 8 4
19 15 3

;
run;

 


*rate of change for each window;
data have; set have;
lag_value=lag5(value);
ROC=abs(value/lag_value-1);
run;

*calculate Moving percentile for each window based on the previous 10 record;
data have; set have;
drop aa1-aa10;
array a{0:9} aa1-aa10;
retain aa:;
pct_60=largest((10-10*60/100+1),of a{*});
pct_70=largest((10-10*70/100+1),of a{*});
pct_80=largest((10-10*80/100+1),of a{*});
pct_90=largest((10-10*90/100+1),of a{*});
a{mod(_n_,10)}=ROC;
run;

data want1; set have;
drop pct_25-pct_100 ;

if ROC>=pct_90 then ROC_rank=5;
else if ROC>=pct_80 then ROC_rank=4;
else if ROC>=pct_70 then ROC_rank=3;
else if ROC>=pct_60 then ROC_rank=1.5;
else ROC_rank=0.1;

run;

Super User
Posts: 11,336

Re: Update lag criteria for ranking Rate of change

I think your are looking for something like:

data have;

   set have;

   array lagvalues L1 - Ln  ; /* n should have the value of the largest number of lags you need. if this exceeds 100 you'll need a different approach*/

   L1= lag1(value);

   L2= Lag2(value);

   ...

   Ln= Lagn(value);

 

   ROC=abs(value/lagvalues[Lagstep]);

   drop L1 - Ln;

run;

Super Contributor
Posts: 406

Re: Update lag criteria for ranking Rate of change

Because in practice, I rank based on 1000+ prior record. so I guess I should make a code with 2 section:

 

The first section is to create a sub datafile that contain 

current record and all needed to get ROC to rank.

 

Then run the Rank code for that sub datafile.

Out put the last record to the final file.

 

And loop through the whole original data.

 

HHC

Respected Advisor
Posts: 4,173

Re: Update lag criteria for ranking Rate of change

For your first data step you need to look-up the value from the day where "day-lagstep". Here one way of how this could work.

proc sql feedback;
  create table want as
  select
    l.*,
    abs(l.value/r.value-1) as ROC
  from have l left join have r
  on l.day=(r.day+l.lagstep)
  order by day
  ;
quit;
Super Contributor
Posts: 406

Re: Update lag criteria for ranking Rate of change

Hi Patrick,

I am not sure it works that way as the out put has the same record as the original one.

Thank you,

HHC

Ask a Question
Discussion stats
  • 4 replies
  • 257 views
  • 0 likes
  • 3 in conversation