Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.

# Re: st: algorithmic question : running sum and computations

 From Scott Merryman To statalist@hsphsun2.harvard.edu Subject Re: st: algorithmic question : running sum and computations Date Fri, 17 Aug 2012 06:42:01 -0500

```This seems to work.  It uses Nick Cox's -tsspell-  (ssc desc tsspell):

clear
input id date str1 product quantity
1       1           A           10
1       2           A           -10
1       1           B            100
1       2           B            -50
1       4           C            15
1       8           C            100
1       9           C            -115
1      10          C            10
1      11          C            -10
end
l, sepby(prod)
bys id product:gen sum = sum(q)

encode prod, gen(prodid)
tsset prodid date
tsspell sum, cond(sum >0)
bys product (date): replace _sp = _sp[_n-1] if _sp == 0
collapse (min) mindate = date (max) maxdate = date ///
(sum) sumquant = quantity, by(id product _sp)
keep if sum == 0
gen d_date = max - min
egen averagelength = mean(d_date), by(id)
l

Scott

On Fri, Aug 17, 2012 at 5:30 AM, Francesco <cariboupad@gmx.fr> wrote:
> Dear Statalist,
>
> I am stuck with a little algorithmic problem and I cannot find an
> simple (or elegant) solution...
>
> I have a panel dataset as (date in days) :
>
> ID    DATE    PRODUCT QUANTITY
> 1       1           A           10
> 1       2           A           -10
>
> 1       1           B            100
> 1       2           B            -50
>
> 1       4           C            15
> 1       8           C            100
> 1       9           C            -115
>
> 1      10          C            10
> 1      11          C            -10
>
>
>
> and I would like to know the average time (in days) it takes for an
> individual in order to complete a full round trip (the variation in
> quantity is zero)
> For example, for the first id we can see that there we have
>
> ID PRODUCT delta_DATE delta_QUANTITY
> 1         A               1=2-1                  0=10-10
> 1         C               5=4-9                  0=15+100-115
> 1         C               1=11-10               0=10-10
>
> so on average individual 1 takes (1+5+1)/3=2.3 days to complete a full
> round trip. Indeed I can discard product B because there is no round
> trip, that is 100-50 is not equal to zero.
>
> My question is therefore ... do you have an idea obtain this simply in
> Stata ? I have to average across thousands of individuals... :)
>
> Many, many thanks for your help and/or suggestions
*
*   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/
```