Spreadsheet string manipulation: diagonal results to row in Stata

[Francois Durant]

Hi,

I have an Excel spreadsheet with "n" sheets with n=[6 12 18 .... t*6], t positive natural number. Each sheet has identical structure and variables. Each of them is basically a diagonal matrix of results (4 rows diagonal) and missing values everywhere else. I would like to reshape each spreadsheet in a way that the diagonal of results is presented in the top (4) rows. How can I do that in Stata? Once I have the solution for 1 sheet, how do I loop through each sheet in the excel file to apply the code to each sheet? I have no idea how to start coding that in Stata. I enclose the entire Excel spreadsheet, a csv file and a screenshot of what I have and what I want. Thanks a lot for your help in advance!

[Phil Bromiley]

You didn't get a quick answer. You'll incrase your chances of a a helpful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output, and sample data using dataex. Do not provide pictures or files. We can't work with pictures, and many won't open files.

You problem appears to be that you have different x variables in some loop. If you are creating the regressions in Stata, you could use a temporary variable so each regression has the same named x. Then you could use outreg2 or estout to build your table.

If you really need to do this with this kind of data, assuming you've got it into Stata, you'll need a macro that includes all the names in your Variables row. Then you can use numbers incremented to move stuff from the original observation (row) the the observation you want. Something like:

local a=1
foreach var in ignore absacc {
replace `var' = `var'[a] in 5/5
local a= `a' + 1
replace `var' = `var'[a] in 6/6
local a=`a' + 1
replace `var' = `var'[a] in 7/7
local a=`a'+1
}