# st: re: help on data manipulation

 From Christopher F Baum To statalist@hsphsun2.harvard.edu Subject st: re: help on data manipulation Date Mon, 27 Sep 2004 20:56:33 -0400

Mitra asked how to tweak a data set to insert a number of '999' entries when revenue exceeded multiples of that amount. Here is a solution:

. l custid rev

+------------------+
| custid revenue |
|------------------|
1. | 1 1,459 |
2. | 1 1,600 |
3. | 1 1,644 |
4. | 2 770 |
5. | 2 720 |
|------------------|
6. | 3 1,226 |
7. | 3 891 |
8. | 4 1,433 |
9. | 4 665 |
10. | 5 1,134 |
|------------------|
11. | 5 1,443 |
12. | 6 792 |
13. | 6 1 |
14. | 7 1,526 |
15. | 7 897 |
|------------------|
16. | 8 1,006 |
17. | 8 509 |
18. | 9 809 |
19. | 9 1,214 |
20. | 9 539 |
|------------------|
21. | 10 905 |
22. | 10 377 |
23. | 11 882 |
24. | 11 1,192 |
25. | 12 1,407 |
|------------------|
26. | 12 1,163 |
27. | 13 605 |
28. | 13 1,099 |
29. | 14 1,209 |
30. | 14 1,357 |
|------------------|
31. | 15 9 |
32. | 15 694 |
33. | 16 705 |
34. | 16 458 |
35. | 17 1,135 |
|------------------|
36. | 17 1,299 |
37. | 17 508 |
+------------------+

. collapse (sum) revenue, by(custid)

. postfile upsrev int custid float rev using revs, replace

. su revenue,meanonly

. local ncust = r(N)

. l custid rev

+------------------+
| custid revenue |
|------------------|
1. | 1 4,703 |
2. | 2 1,490 |
3. | 3 2,117 |
4. | 4 2,098 |
5. | 5 2,577 |
|------------------|
6. | 6 793 |
7. | 7 2,423 |
8. | 8 1,515 |
9. | 9 2,562 |
10. | 10 1,282 |
|------------------|
11. | 11 2,074 |
12. | 12 2,570 |
13. | 13 1,704 |
14. | 14 2,566 |
15. | 15 703 |
|------------------|
16. | 16 1,163 |
17. | 17 2,942 |
+------------------+

. forv i=1/`ncust' {
2. local c = custid[`i']
3. local t = revenue[`i']
4. while `t'>999 {
5. post upsrev (`c') (999)
6. local t = `t' - 999
7. }
8. post upsrev (`c') (`t')
9. }

. postclose upsrev

.
.
end of do-file

. use revs,clear

. l

+--------------+
| custid rev |
|--------------|
1. | 1 999 |
2. | 1 999 |
3. | 1 999 |
4. | 1 999 |
5. | 1 707 |
|--------------|
6. | 2 999 |
7. | 2 491 |
8. | 3 999 |
9. | 3 999 |
10. | 3 119 |
|--------------|
11. | 4 999 |
12. | 4 999 |
13. | 4 100 |
14. | 5 999 |
15. | 5 999 |
|--------------|
16. | 5 579 |
17. | 6 793 |
18. | 7 999 |
19. | 7 999 |
20. | 7 425 |
|--------------|
21. | 8 999 |
22. | 8 516 |
23. | 9 999 |
24. | 9 999 |
25. | 9 564 |
|--------------|
26. | 10 999 |
27. | 10 283 |
28. | 11 999 |
29. | 11 999 |
30. | 11 76 |
|--------------|
31. | 12 999 |
32. | 12 999 |
33. | 12 572 |
34. | 13 999 |
35. | 13 705 |
|--------------|
36. | 14 999 |
37. | 14 999 |
38. | 14 568 |
39. | 15 703 |
40. | 16 999 |
|--------------|
41. | 16 164 |
42. | 17 999 |
43. | 17 999 |
44. | 17 944 |
+--------------+

Kit Baum, Boston College Economics baum@bc.edu
http://fmwww.bc.edu/ec/baum.php

*
* 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/