<>
Mike:
I tried to find a more compact way to do this, but I couldn't reduce the number of lines in the -forvalues- loop in the code below ((Your condition of needing to sum revenue across all 'other' observations within 14 days, including that date, but not that observation, was the tricky part for me)).
Others may have more efficient suggestions, but this does the trick:
*************
clear
input id str20 date revenue
1 "8-Mar-07" 1
2 "15-Mar-07" 2
3 "15-Mar-07" 3
4 "22-Mar-07" 4
5 "29-Mar-07" 5
6 "29-Mar-07" 6
7 "5-Apr-07" 7
8 "5-Apr-07" 8
end
//format date//
g date2 = date(date, "DM20Y")
format date2 %td
//create newvar//
g newvar = .
forval v= 1/`=_N' {
g diff`v' = `=date2[`v']'-date2
replace diff`v' = . if [_n]==`v'
egen newvar`v' = total(revenue) /*
*/ if inrange(diff`v', 0, 14)
egen x`v' = max(newvar`v')
replace newvar`v' = x`v'
replace newvar`v' = . if [_n]!=`v'
egen m`v' = min(newvar`v')
replace newvar`v' = 0 if m`v'==.
replace newvar = newvar`v' if [_n]==`v'
drop m`v' x`v' diff`v' newvar`v'
}
li
*************
Good luck,
Eric
__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
[email protected]
Office: +979.845.6754
On Dec 27, 2009, at 10:11 PM, Mike Kim wrote:
> Dear all,
>
> I would like to create a variable that adds all revenues from all OTHER id
> within the past 14 days (including current date). How can I create the
> "newvar" in the following example?
>
> id date revenue newvar
> 1 8-Mar-07 1 0
> 2 15-Mar-07 2 4 (=1+3)
> 3 15-Mar-07 3 3 (=1+2)
> 4 22-Mar-07 4 6 (=1+2+3)
> 5 29-Mar-07 5 15 (=2+3+4+6)
> 6 29-Mar-07 6 14 (=2+3+4+5)
> 7 5-Apr-07 7 23 (=4+5+6+8)
> 8 5-Apr-07 8 22 (=4+5+6+7)
> ......
>
>
> Thank you in advance.
> Mike.
>
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
