# Re: st: Creating a variable

 From Eric Booth To statalist@hsphsun2.harvard.edu Subject Re: st: Creating a variable Date Mon, 28 Dec 2009 02:01:26 -0600

<>

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
ebooth@ppri.tamu.edu
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)
> ......
>
>