I am using the World Values Survey, which has a repeated cross sectional design. Different countries were included in different survey rounds, and the questions I am interested in were asked in some rounds but not others. I am trying to produce tables as below, with a table for each country on a new sheet, and each question of interest a new excel file.
year x year y year z
A great deal a% b% c%
Quite a lot d% e% f%
Not much g% h% i%
Not at all j% k% l%
100% 100% 100%
My code is:
Code:
foreach x of varlist var1-var9 {  //the questions I want
    forvalues y =1/38 {    // the countries
putexcel set name`x', sheet(`y') modify //setting up excel as a file per variable and a sheet per country
tabulate `x' year2 [aweight = popweight] if cntry==`y', column nofreq matcell(cell`x') matcol(years) //running the tabulation. The matrix seems to store only counts despite the code specifying that I want column percentages

matrix ctot1`x'=J(1, rowsof(cell`x'),1)*cell`x' // generating the column totals. This line is ripped straight from the internet and I don't completely understand how it gets the totals, but it does seem to work
matrix ctot2`x'=J(11, 1, 1) // the column totals are a one line matrix, need the matrix size to be the same as the values from the tabulation
matrix coltotal`x'=ctot1`x'#ctot2`x'


matrix test`x'=J(11,9,0) //making a matrix to store the percentages
forvalues i=1/11 {
    forvalues j=1/9 {
        matrix test`x'[`i', `j']=cell`x'[`i', `j']/coltotal`x'[`i', `j'] //calculating percentages for every cell in the matrix
        }
        }
putexcel B2=matrix(test`x') A2="Agreat deal" A3="Quite a lot" A4="Not very much" A5="None at all" B1=matrix(years) // creating the table in Excel
}
}
The problem I have is that if a given country was never asked a given question, then the sheet for that country will have the results from the previous country. I assume this is because the cell`x' matrix doesn't get updated with a new tabulation if there are no observations. I tried to workaround this by including `y' in my matrix names, eg
Code:
tabulate `x' year2 [aweight = popweight] if cntry==`y', column nofreq matcell(cell`x'`y') matcol(years)
but this breaks -- and stops the whole loop -- if a particular question/country combination never existed. Using "capture" to force it to keep going resulted in all the tables being empty.

Can anyone suggest code that will either not create a table for a country where there is no data, or create a table that makes it very clear that there is no data (instead of duplicating the last country's data)?

Edit: it is not happening only when there are no data for a country for that variable. It is happening even when the "tabulate" command should be creating a new matrix. But it is not happening consistently either -- most of the tables produced are not duplicates.