# st: AW: Help on data manipulation

 From "Christian Holz" To Subject st: AW: Help on data manipulation Date Tue, 28 Sep 2004 01:35:56 +0100

```Hi,

Try the following:

sort customer
by customer: egen rev_sum=sum(revenue)
by customer: gen id2=_n
replace revenue=rev_sum     if (rev_sum<=999)&(id2==1)
drop                        if (rev_sum<=999)&(id2>1)

replace revenue=999          if (rev_sum>999&rev_sum<=1999)&(id2==1)
replace revenue=rev_sum-999  if (rev_sum>999&rev_sum<=1999)&(id2==2)
drop                         if (rev_sum>999&rev_sum<=1999)&(id2>2)

replace revenue=999          if (rev_sum>1999&rev_sum<=2999)&(id2==1)
replace revenue=999          if (rev_sum>1999&rev_sum<=2999)&(id2==2)
replace revenue=rev_sum-1998 if (rev_sum>1999&rev_sum<=2999)&(id2==3)
drop                         if (rev_sum>1999&rev_sum<=2999)&(id2>3)

[to be continued for 3000<=revenue<=3999 if necessary...]

I am sure there is a more "arithmetical" approach, but this one works and
it's too late for soaring thoughts...

Cheers,
Christian

-----Ursprüngliche Nachricht-----
Von: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] Im Auftrag von MITRA PINAKI
(MAR1PXM)
Gesendet: 27 September 2004 22:30
An: statalist@hsphsun2.harvard.edu
Betreff: st: Help on data manipulation

Hello List,

I have a dataset on multiple customer's revenue information like the
following:

Id    customer    revenue
Z11    xx1         y1
Z21    xx1         y2
Z31    xx1         y3
Z12    xx2         k1
Z22    xx2         k2
Z32    xx2         k3  and so on.

I need to create a new data based on the following criteria. If the sum of
revenue by customer is <=999, I need to write
Customer    revenue
xx1         sum of y
xx2         sum of k

But if the sum of revenue by customer is >999 and <=1998, I need to write
Id     Customer    revenue
001    xx1         999
002    xx1         sum of y-999
001    xx2         999
002    xx2         sum of k-999

and if the sum of revenue by customer is >1998 and <=2997, I need to write
Id     Customer    revenue
001    xx1         999
002    xx1         999
003    xx1         sum of y-1998
001    xx2         999
002    xx2         999
003    xx2         sum of k-1998

Can anyone please provide me any help on this?

Thank you,
Pinaki Mitra
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```