topic Re: Rolling percentiles in Statistical Procedures

Rolling percentiles

costasRO — Fri, 25 Feb 2022 15:57:53 GMT

Hi All,

I have some data that look like this:

year month Company x

1970 1 A 0.01

1970 1 B 0.02

1970 1 C 0.03

. ..

1970 2 A 0.05

1970 2 B 0.04

I would like to calculate the moving 90th percentile (p90) of x, across all companies and dates, using 15 years of lagged data.

So, the variable I want would start having non-missing values in 1/1985, and always be calculated from the distribution of 15 years of past data, i.e., the value in 1/1985 would be based on the distribution from 1/1970-12/1984, the value in 2/1985 would be based on 2/1970-1/1985, etc.

Thank you very much in advance.

Costas

Re: Rolling percentiles

PaigeMiller — Fri, 25 Feb 2022 16:06:02 GMT

A simple search of the SAS Community finds a bunch of possible solutions, such as

https://communities.sas.com/t5/SAS-Procedures/Computation-of-backward-moving-percentiles/td-p/129651

I leave it up to you to find others.

Re: Rolling percentiles

costasRO — Fri, 25 Feb 2022 16:09:34 GMT

I have tried this code without success as I do not have a set number of lags (here 252) because the number of companies changes. So the code must determine the lags based on time.

Re: Rolling percentiles

PaigeMiller — Fri, 25 Feb 2022 16:19:00 GMT

You can use this method in which thousands of regressions are performed (scroll down to the section entitled "The BY way for many models"). Instead of running PROC REG, use PROC UNIVARIATE to compute the desired percentiles.

Re: Rolling percentiles

Reeza — Fri, 25 Feb 2022 18:01:20 GMT

You probably want to create a macro then that filters based on the date and loops across the dates.

First combine year/month to create a date variable. If your data set is large, I'd suggest creating an index on that variable.
Then filter based on dates, 15*12 = 180 month using a WHERE statement, and calculate your stats using PROC MEANS/SUMMARY/UNIVARIATE.

Once that is accomplished, you can use a macro loop to iterate through and do all your intervals and combine the results. In this method the number of companies is irrelevant.

Re: Rolling percentiles

PaigeMiller — Fri, 25 Feb 2022 18:03:08 GMT

@Reeza wrote:
You probably want to create a macro ...

Why not BY groups, and no macro at all?

Re: Rolling percentiles

Reeza — Fri, 25 Feb 2022 18:31:11 GMT

Because this screams WRDS data that is big and for BY group and moving stats you typically need to replicate the data as well unless I'm missing some trick for BY groups handling moving windows.

Re: Rolling percentiles

PaigeMiller — Fri, 25 Feb 2022 19:22:46 GMT

Well, that is the BY group trick for rolling statistics, and I guess the best method depends on how big the data set really is.

One thing that is not clear in the description of the problem from @costasRO is exactly what dimensions are we doing the rolling in. Do we want rolling statistics within a company? Or do we perform rolling statistics across all companies and time periods? Or something else?

Re: Rolling percentiles

Reeza — Fri, 25 Feb 2022 19:45:21 GMT

From what I understood it's across all companies, monthly rolling for essentially 180 month window.

They've stated that they can't use arrays as it's across all companies so its not within companies. SASHELP.STOCKS it a fairly similar structure I would guess based on what they've posted so far, I just don't have time to code a solution.

Something roughly like this would be my guess - the macro loop would need to be adjusted.
https://gist.github.com/statgeek/e5e43ff45a4ba1f64d0873ff3bc35974

Re: Rolling percentiles

PaigeMiller — Fri, 25 Feb 2022 19:51:16 GMT

Ok, that's a good example of computing rolling statistics. I'm not convinced it would be faster than the BY variable approach, but unless I choose to program both approaches up, I am not certain which would be faster.

Re: Rolling percentiles

ballardw — Fri, 25 Feb 2022 22:10:54 GMT

@costasRO wrote:

I have tried this code without success as I do not have a set number of lags (here 252) because the number of companies changes. So the code must determine the lags based on time.

One way might be to make sure all the companies are present for all the time periods. Missing values for the value of the variable(s) you are calculating statistics for should be handled correctly. You would have to calculate the lag value I very much doubt your data matches the OP in that other thread. The magic number 252 was based on his data.

Re: Rolling percentiles

Reeza — Sat, 26 Feb 2022 02:45:39 GMT

@costasRO wrote:

Hi All,

I have some data that look like this:

year month Company x

1970 1 A 0.01

1970 1 B 0.02

1970 1 C 0.03

. ..

1970 2 A 0.05

1970 2 B 0.04

I would like to calculate the moving 90th percentile (p90) of x, across all companies and dates, using 15 years of lagged data.

So, the variable I want would start having non-missing values in 1/1985, and always be calculated from the distribution of 15 years of past data, i.e., the value in 1/1985 would be based on the distribution from 1/1970-12/1984, the value in 2/1985 would be based on 2/1970-1/1985, etc.

Thank you very much in advance.

Costas

data have;
set sashelp.stocks;
run;

*sort for faster processing and add index;
proc sort data=have out=have (index=(date));
by date;
run;


options mprint symbolgen;
%macro rolling_p90(ds_in = , window = , ds_out=);

*select minimum date and maximum date;
proc sql noprint;
select min(date) into :min_date
from &ds_in.;
select max(date) into :max_date
from &ds_in.;
quit;

*determine number of months between start and end date;
%let n_months = %sysfunc(intck(month, &min_date, &max_date));

%put &n_months.;
%put &window.;

*start of loop;

%do i=1 %to %eval(&n_months - &window.);

*get start of period;
%let period_start = %sysfunc(intnx(month, &min_date., %eval(&i-1), b));
%put &period_start;
*calculate end of period;
%let period_end = %sysfunc(intnx(month, &period_start., &window.-1, e));

*calculate summary statistics;
proc means data=&ds_in. noprint nway;
where date between  &period_start. and &period_end.;
var open;
output out=summary p90=p90;
run;

*add in date window for comparison;
data summary;
set summary;
period_start = &period_start;
period_end = &period_end.;
format period_: date9.;
run;

*append to main data set;
proc append base=&ds_out data=summary force;
run;

*drop table between loops;
proc sql;
drop table summary;
quit;

%end;

%mend;

%rolling_p90(ds_in = have , window = 24 , ds_out= want);

Re: Rolling percentiles

KachiM — Sat, 26 Feb 2022 05:51:34 GMT

Assuming there are no missing values of X, and that Companies are ignored for the computation, we can use sashelp.stocks as an example data set. Ksharp's code is adapted and just for verification a window size of 4 is used and PCTL = 50 (median) is computed. You could modify to 180 for window and 90 for PCTL.

data stocks;
	set sashelp.stocks (keep =  stock date close);
run;

%let window_size = 4;

data want;
	set stocks;
	array k[0:%eval(&window_size. -1)] _temporary_;
	k[mod(_n_, &window_size)] = close;
	pct_50 = pctl(50,of k[*]);
	if _n_ < &window_size then pct_50 = .; 
run;

Hope this helps.

Re: Rolling percentiles

costasRO — Sat, 26 Feb 2022 13:45:24 GMT

Dear @Reeza

Indeed this is WRDS data, and I have thousands of companies per year-month.

The macro below works perfectly.

Thank you very much!