Weight not found in second iteration of a loop

[Dann Morgan]

All,

I am using Stata 14.1. I have the following code. The code works for the first loop. However, for the second loop - I receive an error message stating svywgt not found r(111). I am not sure why the svywgt is not found after the first loop?

preserve 

forval num = 1/12 { 
collapse (p10) p10_*  (p20) p20_* (p30) p30_* (p40) p40_* (p50) p50_* ///
    (mean) avg_* (p60) p60_* (p70) p70_* (p80) p80_* (p90) p90_* ///
    [aweight = svywgt] if age == 1 & region == `num'
    
export excel using test2.xlsx, firstrow(variables) sheet (`num', replace)

}

restore

Thanks,

[Nick Cox]

Once the data have been collapsed, you would have to read in the original again. Your restore doesn't do that for you, as it's beyond the end of the loop.

Standard debugging advice: Follow the loop around mentally. Execute it for the first time. What are the data now?

But -- good news -- the loop is quite unnecessary. The by() option gives what you want.

preserve  
collapse (p10) p10_*  (p20) p20_* (p30) p30_* (p40) p40_* (p50) p50_* ///    
(mean) avg_* (p60) p60_* (p70) p70_* (p80) p80_* (p90) p90_* ///    
[aweight = svywgt] if age == 1, by(num)      
export excel using test2.xlsx, firstrow(variables) sheet (`num', replace)  
restore

If there are more than 12 regions, you need to complicate that code slightly.

[Dann Morgan]

Thanks Nick. Using -by()- seperates the results by region. The code above then puts this table, showing all the results for each region into one sheet.

I was hoping to export the output (ie p(10) ... p(90)) for each region into a separate excel sheet. So the xlsx document would have 12 separate sheets.

[Nick Cox]

Then you need to read in the original dataset once more after each collapse.

[Dann Morgan]

Thanks Nick - your help is much appreciated.

[Nick Cox]

Note that my fix in #2 was in error. The macro reference wouldn't work. You would need some variant on

 
 export excel using test2.xlsx, firstrow(variables) sheet(whatevernameyouwant, replace)

This isn't what Dann wants, but I thought a fix in order.