Appending multiple tabulate commands to Excel

[Chris Rooney]

Hi all,

I am just wondering if anyone could help me.

At the moment, I have employment data for different industries for multiple waves. What I'd like to do is append each 'tabulate' to an Excel table. Is this possible? My code is currently like this:

set more off

forval x=9/39 {
tab jobindcode if wave==`x' & employerAll==0 [iw=ceweight2]
}

What I'd like is the industries as different rows (Agriculture, Manufacturing, Mining etc) and the different waves being different columns (Wave 1, Wave 2), and then after after each 'tabulate' (there are 31 of them!), the data is appended to the correct column.

[Brendan Cox]

I originally posted something more complex, but after thinking about it further, it seems like this could be resolved fairly simply with -contract- and -reshape-, unless (as is reasonably likely) I am missing something. E.g., using a system data set:

sysuse nlsw88, clear

contract industry occupation
decode occupation, gen(occvar)
replace occvar = strtoname(occvar, 1)
replace occvar = "Missing" if occvar == ""
drop occupation

reshape wide _freq , i(industry) j(occvar) string
rename _freq* *

[Chris Rooney]

I originally posted something more complex, but after thinking about it further, it seems like this could be resolved fairly simply with -contract- and -reshape-, unless (as is reasonably likely) I am missing something. E.g., using a system data set:

sysuse nlsw88, clear

contract industry occupation
decode occupation, gen(occvar)
replace occvar = strtoname(occvar, 1)
replace occvar = "Missing" if occvar == ""
drop occupation

reshape wide _freq , i(industry) j(occvar) string
rename _freq* *

Hi Brendan,

Thanks for the help. I will try it tomorrow.

However, I just want to clear what I'm requesting help on:

I use this post as an example: http://blog.stata.com/2013/09/25/exp...bles-to-excel/

(The 'tabulate foreign' is essentially equivalent to my loop in the first post)

They use the following code:





Now they continue to provide more code



I would like to produce a table like this (with the contents being filled in, obviously)

[Sergiy Radyakin]

forval i=.....
  forval j=....
     summarize if .....i,j
     putexcel .....

Don't forget to use excelcol to obtain the column number when you get for more waves!

[Mike Lacy]

Do you need to automate the move to Excel? If not, and assuming your data are in long format, with each observation being a wave-industry, with jobincode containing the number of employees with, I'd do something simple like -tab2 industry wave [fw = jobincode]- and copy-table-paste into Excel. (Sorry if this is such a simple construction of your problem as to be insulting.) If you have to have an automated -putexcel- solution, that's another matter, as I have not ventured there yet. Regards, Mike

[Chris Rooney]

forval i=.....
forval j=....
summarize if .....i,j
putexcel .....

Don't forget to use excelcol to obtain the column number when you get for more waves!

Sorry could you explain this in more detail please? Or use an example from a freely available dataset? Thanks!

[Brendan Cox]

I originally posted something more complex, but after thinking about it further, it seems like this could be resolved fairly simply with -contract- and -reshape-, unless (as is reasonably likely) I am missing something. E.g., using a system data set:

sysuse nlsw88, clear

contract industry occupation
decode occupation, gen(occvar)
replace occvar = strtoname(occvar, 1)
replace occvar = "Missing" if occvar == ""
drop occupation

reshape wide _freq , i(industry) j(occvar) string
rename _freq* *

Realized I forgot the final command:

export excel // exports entire data set

(see the help files for syntax).