Compute average value excluding the individual value

[Agent1592]

Dear SAS community, I have a panel dataset that has individuals (people), companies and year-quarter variables as identifier. For each individual I would like to compute the average value of var for each company for the respective year-quarter but excluding the value of that individual in the average.

Basically I would like to compute: DIFFijt=[VARijt-average(VARjt(-i))]/ average(VARjt(-i)) where i is the individual, j is the company identifier and t is the year-quarter time identifier.

For example imagine this individual is a CEO and I would like to compare him with all other CEOs. So the average should exclude the CEO i.

Here is the sample code that includes the individual in the average. How do I change this code?

proc sql;
	create table average_var as 
		select avg(var) as avg_var, companyid, qtr
    from have group by companyid, qtr;
	create table diff as 
		select a.*, avg_var
		from have a left join average_var b on a.companyid=b.companyid and a.qtr=b.qtr; 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
;;;;

[PGStats · Accepted solution]

This query should do it:

proc sql;
create table average_var as 
select 
    a.*,
    avg(b.var) as avg_var
from 
    have as a left join
    have as b 
        on  a.companyId=b.companyId and 
            a.qtr=b.qtr and 
            a.individual ne b.individual 
group by a.individual, a.companyid, a.qtr, a.var;
quit;

[Agent1592]

Thanks, at the end shouldn't it be grouping by individual, companyid and qtr since this is the panel dataset. My guess is that we should not be grouping by a.avar?

group by a.individual, a.companyid, a.qtr

[PGStats]

If you remove a.var from the group by clause, you must also remove it from the select clause. Otherwise you will trigger auto-remerging, and get a lot more records than you expect.