Error when using foreach to cut out sample

Question

I want to use foreach to cut out same sample using Stata.

I have written the following code snippet:

foreach i of numlist 0/11 {
preserve
keep id projectno nickname
gen start=`i'*30000+1
gen end=(`i'+1)*30000
outsheet using d:\profile\nickname_`i'.xls in `start'/`end'
restore
}

However, I receive the error below despite having revised it many times:

'/' invalid observation number

How can I correct my code?

If you found my answer helpful, please consider up-voting it with the upper arrow and accepting it using the check-mark. — , Apr 07 '19 at 17:42

score 1 · Answer 1 · 2019-03-29T09:08:30.543

The in statement in the outsheet command is wrong because start and end are generated as variables and not local macros. You need to initialze both start and end as follows:

local start = `i' * 30000 + 1
local end = (`i' + 1) * 30000

Consider the following toy example using Stata's auto toy dataset:

sysuse auto, clear

foreach i of numlist 0/11 {
    preserve
    keep price mpg make
    local start = (`i' * 3) + 1
    local end = (`i' + 1) * 3
    list in `start' / `end'
    restore
}

Results:

     +---------------------------+
     | make          price   mpg |
     |---------------------------|
  1. | AMC Concord   4,099    22 |
  2. | AMC Pacer     4,749    17 |
  3. | AMC Spirit    3,799    22 |
     +---------------------------+

     +-----------------------------+
     | make            price   mpg |
     |-----------------------------|
  4. | Buick Century   4,816    20 |
  5. | Buick Electra   7,827    15 |
  6. | Buick LeSabre   5,788    18 |
     +-----------------------------+

     +------------------------------+
     | make             price   mpg |
     |------------------------------|
  7. | Buick Opel       4,453    26 |
  8. | Buick Regal      5,189    20 |
  9. | Buick Riviera   10,372    16 |
     +------------------------------+

     +------------------------------+
     | make             price   mpg |
     |------------------------------|
 10. | Buick Skylark    4,082    19 |
 11. | Cad. Deville    11,385    14 |
 12. | Cad. Eldorado   14,500    14 |
     +------------------------------+

     +-------------------------------+
     | make              price   mpg |
     |-------------------------------|
 13. | Cad. Seville     15,906    21 |
 14. | Chev. Chevette    3,299    29 |
 15. | Chev. Impala      5,705    16 |
     +-------------------------------+

     +---------------------------------+
     | make                price   mpg |
     |---------------------------------|
 16. | Chev. Malibu        4,504    22 |
 17. | Chev. Monte Carlo   5,104    22 |
 18. | Chev. Monza         3,667    24 |
     +---------------------------------+

     +------------------------------+
     | make             price   mpg |
     |------------------------------|
 19. | Chev. Nova       3,955    19 |
 20. | Dodge Colt       3,984    30 |
 21. | Dodge Diplomat   4,010    18 |
     +------------------------------+

     +-------------------------------+
     | make              price   mpg |
     |-------------------------------|
 22. | Dodge Magnum      5,886    16 |
 23. | Dodge St. Regis   6,342    17 |
 24. | Ford Fiesta       4,389    28 |
     +-------------------------------+

     +----------------------------------+
     | make                 price   mpg |
     |----------------------------------|
 25. | Ford Mustang         4,187    21 |
 26. | Linc. Continental   11,497    12 |
 27. | Linc. Mark V        13,594    12 |
     +----------------------------------+

     +---------------------------------+
     | make                price   mpg |
     |---------------------------------|
 28. | Linc. Versailles   13,466    14 |
 29. | Merc. Bobcat        3,829    22 |
 30. | Merc. Cougar        5,379    14 |
     +---------------------------------+

     +-----------------------------+
     | make            price   mpg |
     |-----------------------------|
 31. | Merc. Marquis   6,165    15 |
 32. | Merc. Monarch   4,516    18 |
 33. | Merc. XR-7      6,303    14 |
     +-----------------------------+

     +------------------------------+
     | make             price   mpg |
     |------------------------------|
 34. | Merc. Zephyr     3,291    20 |
 35. | Olds 98          8,814    21 |
 36. | Olds Cutl Supr   5,172    19 |
     +------------------------------+

Note that it is not necessary the commands preserve, keep and restore to be within your loop as they are one-time operations and repeating them is just inefficient.

score 1 · Answer 2 · answered Mar 28 '19 at 18:11

This isn't a complete answer -- and focuses on a side-issue to your question -- but it won't fit easily into a comment.

Together with changes explained elsewhere, I would change the order of your commands to

preserve
keep id projectno nickname

forval i = 0/11 {
    local start= `i' * 30000 + 1
    local end = (`i' + 1) * 30000
    outsheet using d:\profile\nickname_`i'.xls in `start'/`end'
}

restore

Error when using foreach to cut out sample

2 Answers2