Historical Average for panel data excluding the most recent observation

[Agent1592]

Dear SAS Community:

I have individual, companyid and year-quarter panel dataset. I am looking to compute the historical average of the var by individual but excluding the most recent value. First I compute the cross-sectional average at the individual-qtr level.

proc sql;
	create table have1 as 
		select individual, qtr, avg(var) as avg_var from have group by individual,qtr;
quit;

How can I compute the historical lagged moving average. I am thinking of something like. Is this correct or a datastep is more efficient?

proc sql;
	create table have2 as 
		select individual, qtr, avg(avg_var ) from have1 group by individual;
quit;

data have;
  infile datalines dsd truncover;
  input individual:Z6. companyid:32. qtr:$6. var:32.;
  format individual Z6.;
datalines4;
147283,14392,2013Q4,0.14
534751,14392,2013Q4,0.13
537668,14392,2013Q4,0.03
545356,14392,2013Q4,0.47
561697,14392,2013Q4,0.14
571781,14392,2013Q4,0.02
575241,14392,2013Q4,0
654072,14392,2013Q4,0.07
055939,14378,2013Q4,0.82
507535,14378,2013Q4,0.59
581570,14378,2013Q4,1.02
614746,14378,2013Q4,1.03
146328,14423,2013Q4,7.3
514795,14436,2013Q4,4.57
527979,14436,2013Q4,4.64
612550,14436,2013Q4,4.59
147041,14452,2013Q4,14.58
055673,14447,2013Q4,33.05
150044,14447,2013Q4,32.37
154837,14447,2013Q4,35.25
031792,14479,2013Q4,0.08
073010,14479,2013Q4,0.07
086718,14479,2013Q4,0.11
616787,14479,2013Q4,0.14
009725,14451,2013Q4,4.39
117740,14451,2013Q4,4.1
078132,14459,2013Q4,0
137577,14459,2013Q4,0
163308,14459,2013Q4,0.01
165788,14459,2013Q4,0
609644,14459,2013Q4,0.01
000478,10560,2004Q3,0.05
005469,10560,2004Q3,0.0003
043594,10560,2004Q3,0.001
000478,10560,2004Q4,0.02
005469,10560,2004Q4,0.0294
043594,10560,2004Q4,0.0039
044775,10560,2004Q4,0.0446
071785,10560,2004Q4,0.0181
087125,10560,2004Q4,0.04
629762,10560,2004Q4,0.06
000478,10560,2005Q1,0.02
005469,10560,2005Q1,0.0534
043594,10560,2005Q1,0.0526
044775,10560,2005Q1,0.0215
087125,10560,2005Q1,0.02
629762,10560,2005Q1,0.01
000478,10560,2005Q2,0.04
005469,10560,2005Q2,0.0102
043594,10560,2005Q2,0.0015
044775,10560,2005Q2,0.0295
087125,10560,2005Q2,0
629762,10560,2005Q2,0.04
000478,10560,2005Q3,0.07
005469,10560,2005Q3,0.0782
043594,10560,2005Q3,0.0313
044775,10560,2005Q3,0.0409
087125,10560,2005Q3,0.02
629762,10560,2005Q3,0.07
000478,10560,2005Q4,0.02
005469,10560,2005Q4,0.0316
043594,10560,2005Q4,0.0312
044775,10560,2005Q4,0.0142
071785,10560,2005Q4,0.0217
087125,10560,2005Q4,0.03
629762,10560,2005Q4,0.04
000478,10560,2006Q1,0.01
005469,10560,2006Q1,0.0015
043594,10560,2006Q1,0.0015
044775,10560,2006Q1,0.016
071785,10560,2006Q1,0.028
087125,10560,2006Q1,0.01
629762,10560,2006Q1,0.0015
000478,10560,2006Q2,0.05
005469,10560,2006Q2,0.0067
043594,10560,2006Q2,0.0069
044775,10560,2006Q2,0.0246
087125,10560,2006Q2,0.0246
629762,10560,2006Q2,0.016
005469,10560,2006Q3,0.111
043594,10560,2006Q3,0.1111
044775,10560,2006Q3,0.0664
087125,10560,2006Q3,0.06
629762,10560,2006Q3,0.09
000478,10560,2006Q4,0.55
005469,10560,2006Q4,0.0404
043594,10560,2006Q4,0.1466
044775,10560,2006Q4,0.0119
087125,10560,2006Q4,0.01
000478,10560,2007Q1,0.12
005469,10560,2007Q1,0.1308
043594,10560,2007Q1,0.0572
044775,10560,2007Q1,0.1048
087125,10560,2007Q1,0.04
629762,10560,2007Q1,0.0604
000478,10560,2007Q2,0.05
043594,10560,2007Q2,0.033
044775,10560,2007Q2,0.0023
087125,10560,2007Q2,0.06
629762,10560,2007Q2,0.05
000090,24643,1983Q4,0.15
000513,24643,1983Q4,0.19
006754,24643,1983Q4,0.44
009113,24643,1983Q4,0.28
030981,24643,1983Q4,0.15
033096,24643,1983Q4,0.39
510753,24643,1983Q4,0.39
643737,24643,1983Q4,0.19
647292,24643,1983Q4,0.39
000090,24643,1984Q2,0.32
001273,24643,1984Q2,0.42
009326,24643,1984Q2,0.07
030201,24643,1984Q2,0.22
030981,24643,1984Q2,0.17
577089,24643,1984Q2,0.42
634622,24643,1984Q2,0.22
000090,24643,1984Q3,0.63
000458,24643,1984Q3,0.5
001273,24643,1984Q3,0.33
009326,24643,1984Q3,0.48
030981,24643,1984Q3,0.63
000090,24643,1984Q4,0.66
000458,24643,1984Q4,0.8
000513,24643,1984Q4,0.7
006754,24643,1984Q4,0.72
009326,24643,1984Q4,0.81
018278,24643,1984Q4,1.13
030201,24643,1984Q4,0.98
030981,24643,1984Q4,1.05
577089,24643,1984Q4,0.87
634622,24643,1984Q4,0.82
000090,24643,1985Q1,0.22
000458,24643,1985Q1,0.42
000471,24643,1985Q1,0.32
000474,24643,1985Q1,0.17
006754,24643,1985Q1,0.42
009326,24643,1985Q1,0.77
018278,24643,1985Q1,0.57
030981,24643,1985Q1,0.17
577089,24643,1985Q1,0.22
634622,24643,1985Q1,0.17
643737,24643,1985Q1,0.37
000090,24643,1985Q2,0.34
000458,24643,1985Q2,0.24
000513,24643,1985Q2,0.39
006754,24643,1985Q2,0.44
009326,24643,1985Q2,0.39
018278,24643,1985Q2,0.49
030981,24643,1985Q2,0.29
033096,24643,1985Q2,0.41
577089,24643,1985Q2,0.34
643737,24643,1985Q2,0.39
000090,24643,1985Q3,0.47
000458,24643,1985Q3,0.43
000513,24643,1985Q3,0.38
000605,24643,1985Q3,0.08
006754,24643,1985Q3,0.23
009326,24643,1985Q3,0.09
018278,24643,1985Q3,0.48
030981,24643,1985Q3,0.28
033096,24643,1985Q3,0.53
577089,24643,1985Q3,0.28
000090,24643,1985Q4,2.01
000458,24643,1985Q4,1.42
000462,24643,1985Q4,1.77
000479,24643,1985Q4,0.02
000605,24643,1985Q4,2.16
006754,24643,1985Q4,0.22
009326,24643,1985Q4,1.92
030981,24643,1985Q4,0.02
033096,24643,1985Q4,2.12
577089,24643,1985Q4,1.67
634622,24643,1985Q4,1.92
643737,24643,1985Q4,1.86
000090,24643,1986Q1,0.29
000462,24643,1986Q1,0.49
000479,24643,1986Q1,0.01
000605,24643,1986Q1,0.89
006754,24643,1986Q1,0.09
009326,24643,1986Q1,0.39
030981,24643,1986Q1,0.39
033096,24643,1986Q1,0.64
577089,24643,1986Q1,0.23
634622,24643,1986Q1,0.44
643737,24643,1986Q1,0.19
000090,24643,1986Q2,0.02
000462,24643,1986Q2,0.08
000479,24643,1986Q2,0.03
000481,24643,1986Q2,0.13
000605,24643,1986Q2,0.03
006754,24643,1986Q2,0.28
009326,24643,1986Q2,0.23
030981,24643,1986Q2,0.13
577089,24643,1986Q2,0.38
634622,24643,1986Q2,0.18
643737,24643,1986Q2,0.02
000090,24643,1986Q3,0.06
000298,24643,1986Q3,0.31
000462,24643,1986Q3,0.14
000479,24643,1986Q3,0.03
000481,24643,1986Q3,0.12
009326,24643,1986Q3,0.09
030981,24643,1986Q3,0.24
577089,24643,1986Q3,0.29
634622,24643,1986Q3,0.04
000090,24643,1986Q4,0.41
000297,24643,1986Q4,0.23
000298,24643,1986Q4,0.3
000462,24643,1986Q4,0.96
000479,24643,1986Q4,0.32
000481,24643,1986Q4,0.24
009326,24643,1986Q4,0.45
030981,24643,1986Q4,0.1
033096,24643,1986Q4,0.55
538130,24643,1986Q4,0.15
577089,24643,1986Q4,0.35
634622,24643,1986Q4,0.2
000090,24643,1987Q2,1.69
000297,24643,1987Q2,1.63
000298,24643,1987Q2,1.68
000462,24643,1987Q2,1.53
000471,24643,1987Q2,1.63
000479,24643,1987Q2,1.53
000481,24643,1987Q2,1.57
000485,24643,1987Q2,1.63
006754,24643,1987Q2,1.43
030981,24643,1987Q2,1.48
577089,24643,1987Q2,1.63
634622,24643,1987Q2,1.78
643737,24643,1987Q2,1.68
000090,24643,1987Q3,0.06
000297,24643,1987Q3,0.54
000298,24643,1987Q3,0.24
000462,24643,1987Q3,0.19
000471,24643,1987Q3,0.34
000479,24643,1987Q3,0.24
000481,24643,1987Q3,0.15
000485,24643,1987Q3,0.09
040316,24643,1987Q3,0.01
634622,24643,1987Q3,0.24
643737,24643,1987Q3,0.19
000090,24643,1987Q4,0.23
000297,24643,1987Q4,0.5
000298,24643,1987Q4,0.1
000462,24643,1987Q4,0.25
000471,24643,1987Q4,0.28
000479,24643,1987Q4,0.2
000481,24643,1987Q4,0.3
000485,24643,1987Q4,0.35
005303,24643,1987Q4,0.25
634622,24643,1987Q4,0.16
000090,24643,1988Q1,0.67
000297,24643,1988Q1,1.02
000462,24643,1988Q1,0.52
000471,24643,1988Q1,0.52
000481,24643,1988Q1,0.67
033096,24643,1988Q1,0.92
643737,24643,1988Q1,0.67
000090,24643,1988Q2,0.69
000297,24643,1988Q2,0.59
000298,24643,1988Q2,0.39
000462,24643,1988Q2,0.44
000471,24643,1988Q2,0.49
000479,24643,1988Q2,0.64
000481,24643,1988Q2,0.49
033096,24643,1988Q2,1.44
568633,24643,1988Q2,0.43
634622,24643,1988Q2,0.19
643737,24643,1988Q2,0.44
000090,24643,1988Q3,0.02
000297,24643,1988Q3,0.46
000298,24643,1988Q3,0.26
000462,24643,1988Q3,1.06
000471,24643,1988Q3,0.19
000479,24643,1988Q3,0.09
000481,24643,1988Q3,0.34
000485,24643,1988Q3,0.43
033096,24643,1988Q3,0.36
568633,24643,1988Q3,0.22
634622,24643,1988Q3,0.06
643737,24643,1988Q3,0.34
000090,24643,1988Q4,0.23
000297,24643,1988Q4,0.19
000298,24643,1988Q4,0.17
000471,24643,1988Q4,0.03
000479,24643,1988Q4,0.24
000481,24643,1988Q4,0.13
000485,24643,1988Q4,0.18
004674,24643,1988Q4,0.03
033096,24643,1988Q4,0.13
568633,24643,1988Q4,0.23
577089,24643,1988Q4,0.07
634622,24643,1988Q4,0.37
643737,24643,1988Q4,0.07
000090,24643,1989Q1,0.17
000471,24643,1989Q1,0.52
000474,24643,1989Q1,0.3
000479,24643,1989Q1,0.49
000481,24643,1989Q1,0.42
000485,24643,1989Q1,0.67
004674,24643,1989Q1,0.72
033096,24643,1989Q1,0.32
568633,24643,1989Q1,0.07
577089,24643,1989Q1,0.37
;;;;

[s_lassen · Accepted solution]

I would do it something like this:

proc summary nway data=have;
  class individual qtr;
  var var;
  output out=qtr_sum sum=;
run;

data lagged_avg;
  do count=0 by 1 until(last.individual);
    set qtr_sum;
    by individual;
    if count>0 then
      lagged_avg=_sum/count;
    output;
    _sum=sum(_sum,var);
    end;
  keep individual qtr var lagged_avg;
run;

The first PROC SUMMARY is like your first SQL step, you can also use that. I just used SUMMARY to see if there were any observations with the same individual and quarter. There was not, _FREQ_ was 1 for all obs. But that may not be the case with your real data, of course.

The datastep calculates the average of all the previous VAR values per individual. The data needs to be sorted for that; you will have to add an ORDER BY to your first SQL statement, if you use that, or a PROC SORT i you find out that the first summation is really unnecessary - PROC SUMMARY took care of sorting in my example.