How to calculate the average and total values of previous observations in panel data?

[Meng JI]

Hi all,

I have a question about how to calculate the average and total values of previous observations in panel data?

The data that I have is as below:

[

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float date byte quantity float(wanted1 wanted2)
1 21853 3        .  .
1 21856 2        3  3
1 21868 5      2.5  5
1 21873 1 3.333333 10
2 22220 4        .  .
2 22227 3        4  4
2 22250 1      3.5  7
end
format %td date

variable wanted1 and wanted2 are the variables that I want to generate. wanted1 is the average value of all the previous observations for id1 and id2 and wanted2 is the total value of all the previous observations for id1 and id2.

I tried the code as below:

bysort id (date ): gen wanted3 = sum( quantity )/_n
bysort id (date ): gen wanted4 = sum( quantity )

but these results are not what I actually want. The results are as below:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float date byte quantity float(wanted1 wanted2 wanted3 wanted4)
1 21853 3        .  .        3  3
1 21856 2        3  3      2.5  5
1 21868 5      2.5  5 3.333333 10
1 21873 1 3.333333 10     2.75 11
2 22220 4        .  .        4  4
2 22227 3        4  4      3.5  7
2 22250 1      3.5  7 2.666667  8
end
format %td date

I wonder if anyone knows how to revise the code to get the value in wanted1 and wanted2?

Thank you!

[Ali Atia]

bysort id (date ): gen wanted3 = sum( quantity[_n-1] )/(_n-1) if _n!=1
bysort id (date ): gen wanted4 = sum( quantity[_n-1] ) if _n!=1

[Meng JI]

bysort id (date ): gen wanted3 = sum( quantity[_n-1] )/(_n-1) if _n!=1
bysort id (date ): gen wanted4 = sum( quantity[_n-1] ) if _n!=1

Hi Ali,

Thank you so much for the prompt reply! It works perfectly for me. Exactly what I want.

Thanks!