Export to excel formatting with changing cells ranges
Dear all,
I have a question related to how to export to Excel different datasets (specifically, resultssets) giving format to a set of cells when the range of these cells changes on every datasets. I am using Stata 13 (although my Uni has access to Stata 14) in Windows 10.
To give a background of what I am doing, I have a comparable (sampling design-wise) dataset for 30 countries. For each of these, I am estimating proportions and associated standard errors for the same set of K=20 CATEGORICAL variables. My goal is to create, for every country, an Excel sheet (I must stick to this program for this project) with 3 colums (category, estimated proportion, estimated standard error) and 20 consecutive blocks of rows (one block for each of the 20 variables). Each of these 20 blocks is made up of 1+q_k rows:
1) 1 row indicating the label of the k-th variable (column 1) and nothing on columns 2 and 3
2) q_k additional rows showing the name of the categories for this k-th variable (in the column 1) and its associated proportions (column 2) and standard errors (column 3).
I am sticking to the resultssets approach from Newson, R. (2004) From datasets to resultssets in Stata (http://www.rogernewsonresources.org....4/overhed2.pdf) which, basically, consists on turning the dataset into the desired statistical table. So, for each of the 30 countries, I apply a nested loop through the 20 variables which provides me the variable-especific proportions and standard errors for each category using -parmby- (the q_k rows above). After this, I use -insob- (from SSC) to insert a new observation which will have the variable label at the beginning of this block (the first row of the block). Iterating this procedure I get each of 20 the blocks described above. After appending these, I export them with -export excel- with the sheet() option. A MWE of this is
As expected, the structure of the resultsset for each country looks like
My problem is that I want to apply, for each block of each of these country-specific sheets, italics to the first row (which contains the label of the k-th variable) and indentation the q_k rows (which shows the categories of the k-th variable) in the first column. The complication arises because the range of the cells that I want to format changes in a non-uniform way between countries. This is because the number of categories for a given variable (say, var_k) might be different for every country, given a country-specific codification (e.g. geographical regions differ between countries) or, even in the case when a variable have theorethically a unique codification, a given category might be absent on particular countries. Hence, row i on a given sheet (country) might not refer to the same thing (label or categories) on different sheets. I am pretty sure that specifying these cells manually is not the way to go.
So, my question is: is there any way that I can apply the desired formatting on an Excel file (using export excel or other command) from within Stata that accomodates to this resultssets approach when the range of cells to be formatted change? More specifically, I was wondering if there is solution similar to what can be done in LaTeX; namely to apply between parmby and insob commands
replace category="\hspace{3bp} " + category
which would give me what I want. Is there a similar way to do this in Excel? Just for the record. I am aware that the -putexcel- command allows formatting cells. However, this solution turns out to be very inneficient since it requires me to specify the range of the cells that I wish to format.
Thanks for you time
I have a question related to how to export to Excel different datasets (specifically, resultssets) giving format to a set of cells when the range of these cells changes on every datasets. I am using Stata 13 (although my Uni has access to Stata 14) in Windows 10.
To give a background of what I am doing, I have a comparable (sampling design-wise) dataset for 30 countries. For each of these, I am estimating proportions and associated standard errors for the same set of K=20 CATEGORICAL variables. My goal is to create, for every country, an Excel sheet (I must stick to this program for this project) with 3 colums (category, estimated proportion, estimated standard error) and 20 consecutive blocks of rows (one block for each of the 20 variables). Each of these 20 blocks is made up of 1+q_k rows:
1) 1 row indicating the label of the k-th variable (column 1) and nothing on columns 2 and 3
2) q_k additional rows showing the name of the categories for this k-th variable (in the column 1) and its associated proportions (column 2) and standard errors (column 3).
I am sticking to the resultssets approach from Newson, R. (2004) From datasets to resultssets in Stata (http://www.rogernewsonresources.org....4/overhed2.pdf) which, basically, consists on turning the dataset into the desired statistical table. So, for each of the 30 countries, I apply a nested loop through the 20 variables which provides me the variable-especific proportions and standard errors for each category using -parmby- (the q_k rows above). After this, I use -insob- (from SSC) to insert a new observation which will have the variable label at the beginning of this block (the first row of the block). Iterating this procedure I get each of 20 the blocks described above. After appending these, I export them with -export excel- with the sheet() option. A MWE of this is
Code:
local countries_list country_1 country_2 ... country_30
local vars_list var_1 var_2 ... var_20
foreach country of local countries_list {
use "Dataset `country'.dta", clear
foreach var of local vars_list {
local var_label: variable label `var'
preserve
parmby "proportion `var'", label rename(parm category estimate proportion stderr se) norestore
insob 1 1
replace category="Variable `var': `var_label'" in 1
save "`country' `var'.dta", replace
restore
}
clear
foreach var of local vars_list {
append using "`country' `var'.dta"
}
export excel category proportion se using "Descriptives.xlsx", sheet("`country'") firstrow(variables) sheetmodify
}
My problem is that I want to apply, for each block of each of these country-specific sheets, italics to the first row (which contains the label of the k-th variable) and indentation the q_k rows (which shows the categories of the k-th variable) in the first column. The complication arises because the range of the cells that I want to format changes in a non-uniform way between countries. This is because the number of categories for a given variable (say, var_k) might be different for every country, given a country-specific codification (e.g. geographical regions differ between countries) or, even in the case when a variable have theorethically a unique codification, a given category might be absent on particular countries. Hence, row i on a given sheet (country) might not refer to the same thing (label or categories) on different sheets. I am pretty sure that specifying these cells manually is not the way to go.
So, my question is: is there any way that I can apply the desired formatting on an Excel file (using export excel or other command) from within Stata that accomodates to this resultssets approach when the range of cells to be formatted change? More specifically, I was wondering if there is solution similar to what can be done in LaTeX; namely to apply between parmby and insob commands
replace category="\hspace{3bp} " + category
which would give me what I want. Is there a similar way to do this in Excel? Just for the record. I am aware that the -putexcel- command allows formatting cells. However, this solution turns out to be very inneficient since it requires me to specify the range of the cells that I wish to format.
Thanks for you time
