Macro for proc univariate statistic from overlapping time periods

[rfrancis]

Hello  SAS community,

I am attempting to build a macro (using SAS 9.4) that will generate the coefficient of variation from PROC UNIVARIATE for overlapping time periods (annual data, so blocks consist of multiple years). For example, say a block of years is 1988-1991, then the next block will be 1989-1992 .... ending with 2015-2018. Big idea is to build a data set where each observation contains a block ID, N (# obs) and CV (Coeff of Variation for a block say 1988-1991).

I am working with the attached macro (where I credit another author, as I am borrowing his/her code as a starting point for this project), then added my own modifications.

A subset of 100 obs from my data set appears below where the columns represent firm_id, year, quarter and cash_flow.

As written, the SAS log generates no errors, and the results appear annually rather than as a group or block of years (e.g., each obs reports the CV for a single year instead of CV for a group of years).

I am grateful for any insight you might have.

Thank you,

Rick

datalines;
001003 1988 1 -1.611
001003 1988 2 -0.821
001003 1988 3 0.814
001003 1988 4 -0.482
001003 1989 2 .
001003 1989 4 .
001003 1990 1 -0.367
001003 1990 2 -0.146
001003 1990 3 0.128
001004 1988 1 .
001004 1988 2 -0.163
001004 1988 3 -2.961
001004 1988 4 4.214
001004 1989 1 -6.536
001004 1989 2 7.916
001004 1989 3 5.385
001004 1989 4 12.606
001004 1990 1 9.051
001004 1990 2 2.079
001004 1990 3 29.324
001004 1990 4 -3.563
001004 1991 1 -0.546
001004 1991 2 -4.623
001004 1991 3 -0.884
001004 1991 4 14.867
001004 1992 1 -0.701
001004 1992 2 0.98
001004 1992 3 5.248
001004 1992 4 11.279
001004 1993 1 6.462
001004 1993 2 2.21
001004 1993 3 -16.931
001004 1993 4 14.956
001004 1994 1 -8.87
001004 1994 2 -5.816
001004 1994 3 11.472
001004 1994 4 18.469
001004 1995 1 7.026
001004 1995 2 -2.574
001004 1995 3 9.293
001004 1995 4 11.015
001004 1996 1 10.242
001004 1996 2 0.871
001004 1996 3 -7.131
001004 1996 4 5.549
001004 1997 1 1.489
001004 1997 2 -19.391
001004 1997 3 7.739
001004 1997 4 32.986
001004 1998 1 15.101
001004 1998 2 -9.49
001004 1998 3 18.175
001004 1998 4 4.739
001004 1999 1 -20.67
001004 1999 2 15.123
001004 1999 3 11.214
001004 1999 4 4.384
001004 2000 1 -7.743
001004 2000 2 17.893
001004 2000 3 -8.235
001004 2000 4 44.178
001004 2001 1 -26.131
001004 2001 2 9.186
001004 2001 3 1.333
001004 2001 4 -17.703
001004 2002 1 4.796
001004 2002 2 4.343
001004 2002 3 2.749
001004 2002 4 22.845
001004 2003 1 9.035
001004 2003 2 25.139
001004 2003 3 -32.777
001004 2003 4 13.175
001004 2004 1 5.552
001004 2004 2 3.019
001004 2004 3 19.458
001004 2004 4 22.909
001004 2005 1 -21.698
001004 2005 2 -1.618
001004 2005 3 -38.759
001004 2005 4 21.593
001004 2006 1 -1.851
001004 2006 2 6.256
001004 2006 3 -3.505
001004 2006 4 -22.139
001004 2007 1 2.06
001004 2007 2 -51.21
001004 2007 3 22.402
001004 2007 4 43.674
001004 2008 1 -10.499
001004 2008 2 18.626
001004 2008 3 -16.055
001004 2008 4 72.379
001004 2009 1 34.122
001004 2009 2 24.014
001004 2009 3 36.511
001004 2009 4 58.509
001004 2010 1 7.218
001004 2010 2 22.594
001004 2010 3 30.09
001004 2010 4 48.696
001004 2011 1 -25.612
001004 2011 2 30.411
001004 2011 3 13.432
001004 2011 4 75.986
001004 2012 1 33.2
001004 2012 2 27.1
001004 2012 3 27.5
001004 2012 4 75.1
001004 2013 1 27.5
001004 2013 2 38.8
001004 2013 3 10.4
001004 2013 4 63.1
001004 2014 1 15
001004 2014 2 16
001004 2014 3 -22
001004 2014 4 -52
001004 2015 1 -63.7
001004 2015 2 47.8
001004 2015 3 2
001004 2015 4 46
001004 2016 1 -1.1
001004 2016 2 -0.1
001004 2016 3 -10
001004 2016 4 33
001004 2017 1 -20.6
001004 2017 2 10.7
001004 2017 3 42.8
001004 2017 4 31.4
001004 2018 1 -27
001004 2018 2 -8.5
001004 2018 3 60
001009 1988 4 .
001009 1989 1 0.615
001009 1989 2 -0.118
001009 1989 3 0.184
001009 1989 4 1.545
001009 1990 1 0.504
001009 1990 2 -0.218
001009 1990 3 1.362
001009 1990 4 0.427
001009 1991 2 .
001009 1991 3 1.216
001009 1991 4 1.749
001009 1992 1 1.241
001009 1992 2 -0.928
001009 1992 3 2.947
001009 1992 4 2.764
001009 1993 1 -0.822
001009 1993 2 1.492
001009 1993 3 0.187
001009 1993 4 2.59
001009 1994 1 0.858
001009 1994 2 1.495
001009 1994 3 -0.354
001009 1994 4 -0.074
001009 1995 1 -2.266
001009 1995 2 1.74
001009 1995 3 -4.336
001010 1988 4 .
001010 1989 1 -0.416
001010 1989 2 50.204
001010 1989 3 13.26
001010 1989 4 37.784
001010 1990 1 6.117
001010 1990 2 5.8
001010 1990 3 11.777
001010 1990 4 4.683
001010 1991 1 5.861
001010 1991 2 13.584
001010 1991 3 20.175
001010 1991 4 8.949
001010 1992 1 11.835
001010 1992 2 6.896
001010 1992 3 29.445
001010 1992 4 14.032
001010 1993 1 9.624
001010 1993 2 -17.979
001010 1993 3 -32.509
001010 1993 4 49.569
001010 1994 1 23.222
001010 1994 2 19.236
001010 1994 3 14.442
001010 1994 4 -20.7
001010 1995 1 46.4
001010 1995 3 .
001010 1995 4 20.5
001010 1996 1 16.6
001010 1996 2 -2.2
001010 1996 3 44.2
001010 1996 4 21.4
001010 1997 1 14.1
001010 1997 2 48
001010 1997 3 17.9
001010 1997 4 10.9
001010 1998 1 50.4
001010 1998 2 1.6
001010 1998 3 9
001010 1998 4 31.4
001010 1999 1 26.5
001010 1999 2 15.4
001010 1999 4 .
001010 2000 1 30.6
001010 2000 2 -2.1
001010 2000 3 -13.9
001010 2000 4 22.5
001010 2001 1 16.1
001010 2001 2 26.2
001010 2001 3 21.3
001010 2001 4 50.6
001010 2002 1 34.3
001010 2002 2 -1.5
001010 2002 3 12
001010 2002 4 120.5
001010 2003 1 72.6
001010 2003 2 -28.5
001010 2003 3 28.6
001010 2003 4 -118.4
001010 2004 1 -48.2
001010 2004 2 89.1
001011 1988 4 .
001011 1989 1 -0.223
001011 1989 2 0.086
001011 1989 3 0.042
001011 1989 4 -0.43
001011 1990 1 -0.32
001011 1990 2 -0.236
001011 1990 3 0.012
001011 1990 4 -0.237
001011 1991 1 0.092
001011 1991 2 -0.276
001011 1991 3 -0.009
001011 1991 4 -0.05
001011 1992 1 -0.398
001011 1992 2 -0.393
001011 1992 3 0.225
001011 1992 4 -0.375
001011 1993 1 -0.326
001011 1993 2 -0.055
001011 1993 3 -0.765
001011 1993 4 0.329
001011 1994 1 -1.71
001011 1994 2 -1.453
001011 1994 3 0.58
001011 1994 4 5.302
001011 1995 1 1.144
001011 1995 2 -1.703
001012 1988 4 .
001012 1989 1 -0.422
001012 1989 2 0.487
001012 1989 3 3.143
001012 1989 4 -0.473
001012 1990 1 -0.32
001013 1988 1 .
001013 1988 2 4.555
001013 1988 3 8.855
001013 1988 4 9.31
001013 1989 1 2.9
001013 1989 2 7.216
001013 1989 3 2.682
001013 1989 4 5.102
001013 1990 1 7.139
001013 1990 2 5.233
001013 1990 3 9.903
001013 1990 4 12.42
001013 1991 1 7.194
001013 1991 2 3.232
001013 1991 3 11.157
001013 1991 4 15.12
001013 1992 1 3.759
001013 1992 2 7.628
001013 1992 3 11.771
001013 1992 4 11.597
001013 1993 1 9.565
001013 1993 2 0.021
001013 1993 3 12.19
001013 1993 4 7.672
001013 1994 1 20.251
001013 1994 2 1.941
001013 1994 3 15.75
001013 1994 4 20.259
001013 1995 1 6.023
001013 1995 2 -2.718
001013 1995 3 18.226
001013 1995 4 22.728
001013 1996 1 4.874
001013 1996 2 -1.478
001013 1996 3 23.333
001013 1996 4 36.728
001013 1997 1 24.223
001013 1997 2 14.357
001013 1997 3 27.754
001013 1997 4 13.379
001013 1998 1 23.547
001013 1998 2 20.203
001013 1998 3 9.79
001013 1998 4 28.538
001013 1999 1 104.477
001013 1999 2 85.555
001013 1999 3 60.486
001013 1999 4 81.763
001013 2000 1 41.582
001013 2000 2 85.354
001013 2000 3 -47.225
001013 2000 4 141.689
001013 2001 1 -87.3
001013 2001 2 27.8
001013 2001 3 71.6
001013 2001 4 82.9
001013 2002 1 132.1
001013 2002 2 82.4
001013 2002 3 -49.5
001013 2002 4 -104.5
001013 2003 1 50.2
001013 2003 2 -36.2
001013 2003 3 16.6
001013 2003 4 8.3
001013 2004 1 -6.2
001013 2004 2 2.9
001013 2004 3 -15.6
001013 2004 4 22
001013 2005 1 -16.1
001013 2005 2 -1.2
001013 2005 3 25.2
001013 2005 4 50.7
001013 2006 1 -13.7
001013 2006 2 43.6
001013 2006 3 21.7
001013 2006 4 35.5
001013 2007 1 25.8
001013 2007 2 41.1
001013 2007 3 28.8
001013 2007 4 46.1
001013 2008 1 5.1
001013 2008 2 49.4
001013 2008 3 56
001013 2008 4 65.1
001013 2009 1 -19.2
001013 2009 2 24.4
001013 2009 3 47.5
001013 2010 1 .
001013 2010 2 2.6
001013 2010 3 85.3
001013 2010 4 34.4
001017 1988 1 -6.5
001017 1988 2 -3.069
001017 1988 3 2.237
001017 1988 4 -0.624
001017 1989 1 3.536
001017 1989 2 0.4
001017 1989 3 7.46
001017 1989 4 1.047
001017 1990 1 1.165
001017 1990 2 1.358
001017 1990 3 5.141
001017 1990 4 2.33
001017 1991 1 3.938
001017 1991 2 5.732
001017 1991 3 0.547
001017 1991 4 2.031
001017 1992 1 -0.331
001017 1992 2 0.084
001017 1992 3 0.694
001017 1992 4 2.608
001017 1993 1 2.607
001017 1993 2 1.681
001017 1993 3 4.947
001017 1993 4 -0.419
001017 1994 1 1.287
001017 1994 2 1.859
001017 1994 3 -2.408
001017 1994 4 1.087
001017 1995 1 -2.619
001017 1995 2 0.176
001017 1995 3 3.299
001019 1988 4 .
001019 1989 1 0.648
001019 1989 2 1.172
001019 1989 3 0.991
001019 1989 4 0.812
001019 1990 1 1.435
001019 1990 2 1.02
001019 1990 3 1.668
001019 1990 4 -0.04
001019 1991 1 0.589
001019 1991 2 1.105
001019 1991 3 0.893
001019 1991 4 0.903
001019 1992 1 0.424
001019 1992 4 .
001019 1993 4 .
001019 1994 4 .
001019 1995 4 .
001019 1996 4 .
001019 1997 4 .
001019 1998 4 .
001019 1999 4 .
001019 2000 4 .
001019 2001 4 .
001020 1988 4 .
001020 1989 1 13.263
001020 1989 2 7.614
001020 1989 3 14.021
001020 1989 4 10.284
001020 1990 1 -11.182
001021 1988 4 .
001021 1989 1 -0.522
001021 1989 2 -0.903
001021 1989 3 0.643
001021 1989 4 2.066
001021 1990 1 -0.195
001021 1990 2 -0.301
001021 1990 3 0.488
001021 1990 4 0.873
001021 1991 2 .
001021 1991 3 0.146
001021 1991 4 1.036
001021 1992 1 0.9
001021 1992 2 1.959
001021 1992 3 -0.4
001021 1992 4 1.348
001021 1993 1 -0.484
001021 1993 2 0.911
001021 1993 3 0.831
001021 1993 4 -0.12
001021 1994 1 0.156
001021 1994 2 0.232
001021 1994 3 0.454
001021 1994 4 0.828
001021 1995 1 0.497
001021 1995 2 0.718
001021 1995 3 0.546
001021 1995 4 1.152
001021 1996 1 -0.926
001021 1996 2 -0.33
001021 1996 4 .
001021 1997 1 1.875
001021 1997 2 0.662
001021 1997 3 1.953
001021 1997 4 0.983
001021 1998 1 0.383
001021 1998 2 -0.368
001021 1998 3 -1.377
001021 1998 4 0.704
001021 1999 1 0.109
001021 1999 2 -0.312
001021 1999 3 0.266
001021 1999 4 0.26
001021 2000 1 0.489
001021 2000 2 0.995
001021 2000 3 -0.166
001021 2000 4 0.239
001021 2001 1 0.339
001021 2001 2 0.63
001021 2001 3 -0.095
001021 2001 4 0.148
001021 2002 1 0.287
001021 2002 2 0.529
001021 2002 3 0.157
001021 2002 4 0.177
001021 2003 1 -0.349
001021 2003 2 0.761
001021 2003 3 -0.046
001021 2003 4 0.096
001021 2004 1 0.601
001021 2004 2 0.577
001021 2004 3 0.244
001021 2004 4 -0.541
001021 2005 1 0.095
001021 2005 2 0.594
001021 2005 3 0.512
001021 2005 4 -0.076
001021 2006 1 -0.335
001021 2006 2 1.171
001021 2006 3 0.291
001021 2006 4 -0.193
001021 2007 1 -0.876
001021 2007 2 0.574
001021 2007 3 0.498
001021 2007 4 -1.74
001021 2008 1 -0.685
001021 2008 2 -0.154
001021 2008 3 -0.754
001021 2008 4 -0.703
001021 2009 1 -0.861
001021 2009 2 2.252
001021 2009 3 -0.854
001023 1988 1 .
001023 1988 2 6.817
001025 1988 1 .
001025 1988 2 -0.243
001025 1988 3 -0.128
001025 1988 4 -0.28
001025 1989 1 -0.292
001025 1989 2 -0.746
001025 1989 3 0.135
001025 1989 4 0.374
001025 1990 1 0.5
001025 1990 2 -0.025
001025 1990 3 -0.443
;;;;

[Reeza · Accepted solution]

Are you set with a macro? Any reason to not use PROC EXPAND (if you have SAS ETS licensed) or a MultiLabel Format which would do this much easier?

data want;
array p{0:3} _temporary_;
set have; 
by ID;
if first.ID then do; 
call missing(of p{*}); 
counter=1;
end;
counter+1;
p{mod(_n_,4)} = value;
if counter>3 then do;
YearRange = catx('/', year, year-3);
moving_average = mean(of p{*});
moving_cv = cv(of p{*});
end;
run;

[Reeza]

And I think you forgot the first few lines of your code there?

[rfrancis]

I think the code is in the attachment .... maybe the attachment did not go through.

[Reeza]

@rfrancis did you see the array code I posted? That should work perfectly fine for the data you posted. You didn't include variable names so you'll have to figure out how to map it but in general, moving statistics are trivial and do not need a macro. 

[rfrancis]

Hi Reeza, I see your array code, thank you!!

[PaigeMiller]

Macros are not needed. If you search the Internet for "rolling window regression" you can find code to do this without macros. It's the same concept, you can do this as "rolling window PROC UNIVARIATE".

[rfrancis]

I've seen the rolling regression code on the web. The pgms are generally quite long and much more complicated than what is needed for my application. I hesitate to say my application is simple, but in a relative sense, my application is much more simple than the pgms which appear on the web.

[PaigeMiller]

Okay, I disagree that the problem is as simple as your are implying, unless you have access to PROC EXPAND. Nevertheless, this code is about as simple as it gets: http://pages.stern.nyu.edu/~adesouza/sasfinphd/index/node25.html. If you find a solution that is simpler, please let me know.