st: re: help on data manipulation

[Christopher F Baum <baum@bc.edu>]

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/