topic Re: Standard deviation of the past 12 quarters in SAS Programming

Standard deviation of the past 12 quarters

joon1 — Mon, 26 Feb 2024 23:26:38 GMT

Dear Madam/Sir,

I would like to standard deviation of cash flow (cf) in the past three years (twelve quarters) using the data example below. The modified code below is not working well. Any advice will be highly appreciated.

data want;
set have;
by gvkey;
array t {12} _temporary_;
if first.gvkey then call missing(of t{*});
do fy=sum(lag(quarter),1) to quarter-1;
t{1+mod(fy,12)}=.;
end;
t{1+mod(fyear,12)}=cf;
if n(of t{*})>1 then cfstd=std(of t{*});
run;

gvkey year quarter cf

1004	1988	1	-0.0032
1004	1988	2	-0.0035
1004	1988	3	-0.0119
1004	1988	4	0.00026
1004	1989	1	-0.018
1004	1989	2	0.00369
1004	1989	3	0.01763
1004	1989	4	0.04986
1004	1990	1	0.02346
1004	1990	2	0.02906
1004	1990	3	0.10553
1004	1990	4	0.09709
1004	1991	1	-0.0015
1004	1991	2	-0.0132
1004	1991	3	-0.0153
1004	1991	4	0.02229
1004	1992	1	-0.0018
1004	1992	2	0.0007
1004	1992	3	0.01478
1004	1992	4	0.04602
1004	1993	1	0.01721
1004	1993	2	0.02124
1004	1993	3	-0.0203
1004	1993	4	0.01604
1008	1994	1	-0.0216
1008	1994	2	-0.0355
1008	1994	3	-0.0076
1008	1994	4	0.03583
1008	1995	1	0.01708
1008	1995	2	0.01056
1008	1995	3	0.03202
1008	1995	4	0.05655
1008	1996	1	0.02278
1008	1996	2	0.02372
1008	1996	3	0.0076
1008	1996	4	0.018
1008	1997	1	0.00274
1008	1997	2	-0.0305
1008	1997	3	-0.0153
1008	1997	4	0.03404

Thank you so much for your help.

Joon1

Re: Standard deviation of the past 12 quarters

mkeintz — Tue, 27 Feb 2024 03:58:13 GMT

If there are no holes in the quarterly time series, and you only want full 12-quarter windows, then:

data have;
  input gvkey year quarter cf;
datalines;
1004	1988	1	-0.0032
1004	1988	2	-0.0035
1004	1988	3	-0.0119
1004	1988	4	0.00026
1004	1989	1	-0.018
1004	1989	2	0.00369
1004	1989	3	0.01763
1004	1989	4	0.04986
1004	1990	1	0.02346
1004	1990	2	0.02906
1004	1990	3	0.10553
1004	1990	4	0.09709
1004	1991	1	-0.0015
1004	1991	2	-0.0132
1004	1991	3	-0.0153
1004	1991	4	0.02229
1004	1992	1	-0.0018
1004	1992	2	0.0007
1004	1992	3	0.01478
1004	1992	4	0.04602
1004	1993	1	0.01721
1004	1993	2	0.02124
1004	1993	3	-0.0203
1004	1993	4	0.01604
1008	1994	1	-0.0216
1008	1994	2	-0.0355
1008	1994	3	-0.0076
1008	1994	4	0.03583
1008	1995	1	0.01708
1008	1995	2	0.01056
1008	1995	3	0.03202
1008	1995	4	0.05655
1008	1996	1	0.02278
1008	1996	2	0.02372
1008	1996	3	0.0076
1008	1996	4	0.018
1008	1997	1	0.00274
1008	1997	2	-0.0305
1008	1997	3	-0.0153
1008	1997	4	0.03404
run;

%let win_size=12;
data want;
  set have;
  by gvkey;
  array qtr_window {0:%eval(&win_size-1)} _temporary_;

  if first.gvkey then call missing(of qtr_window{*});
  qtr_window{mod(_n_,&win_size)}=cf;
  if nmiss(of qtr_window{*})=0;  /* Full windows only */
  cfstd=std(of qtr_window{*});
run;

This code generates 12-quarter STDEV's for the current quarter and previous 11.

Re: Standard deviation of the past 12 quarters

Ksharp — Tue, 27 Feb 2024 05:35:08 GMT

Here is an example:

data data2;
call streaminit(123);
do permno=1 to 10;
 do date='01jan1980'd to '01jan1990'd;
  return=rand('uniform');
  if weekday(date) not in ( 1 7 ) then output;
end;
end;
format date yymmdd10.;
run;

 

 
data want;
 array r{%sysevalf('01jan1960'd):%sysevalf('06mar2025'd)} _temporary_;
 array x{%sysevalf('01jan1960'd):%sysevalf('06mar2025'd)} _temporary_;
 call missing(of r{*} x{*});
do until(last.permno);
 set data2;
 by permno;
 r{date}=return;
end;

do until(last.permno);
 set data2;
 by permno;
 do i=intnx('year',date,-3,'s') to date;
  x{i}=r{i};
 end;
 std=std(of x{*});
 output;
end;
drop i;
run;

Re: Standard deviation of the past 12 quarters

PaigeMiller — Tue, 27 Feb 2024 12:15:43 GMT

You can use PROC EXPAND (if you have SAS/ETS) and the programming is much easier. Here is an example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/etsug/etsug_expand_examples04.htm use the MOVSTD 12 transformation instead of MOVAVE 3

Re: Standard deviation of the past 12 quarters

joon1 — Tue, 27 Feb 2024 18:55:49 GMT

Thanks a lot for your help, mkeintz. Have a great day.

Re: Standard deviation of the past 12 quarters

joon1 — Tue, 27 Feb 2024 18:56:49 GMT

Thanks a lot for your help, Ksharp, Have a great day.

Re: Standard deviation of the past 12 quarters

joon1 — Tue, 27 Feb 2024 18:56:50 GMT

Thanks a lot for your help, PaigeMiller. Have a great day.

Re: Standard deviation of the past 12 quarters

Ksharp — Wed, 28 Feb 2024 09:32:22 GMT

Sorry. Paige,
I don't have access to SAS/ETS.
That need money to buy.

Re: Standard deviation of the past 12 quarters

PaigeMiller — Wed, 28 Feb 2024 10:15:54 GMT

No need to apologize.