Roger Newson's listtab suite of commands

[Maria Boutchkova]

Dear statalisters,

I have made some progress in figuring our how to use -listtab (and the rest of the auxiliary commands written by Roger Newson) to list data in RTF format for later embedding in a Word document, but I am not quite there yet.

The problems are as follows:
1. The data I am trying to output are strings. I am guessing that something goes wrong with the RTF prefixes and suffixes and the output is truncated at the first space and also unusual characters are being added (not in the example output file produced by the code below, but with my longer original entity names).
2. Some rows of the output table do not align with the rest.
3. I am unable to pass as gap row labels the string elements of a local macro created by -levelsof in the -ingap command. I guess I'm missing something about string elements of a macro enclosed in quotes.
4. (small and not strictly pertaining to -listtab) When counting the number of elements stored in the local macro created by -levelsof, the count is one more than it should be by construction. Either I am not seeing something obvious and simple, or this is something to be kept in mind in general when using -levelsof.

************
* gen data *
************

* 50 locations
capture set obs 50
gen loc_num = _n

label define loc_label 1 "A" 2 "B" 3 "C" 4 "D" 5 "E" 6 "F" 7 "G" 8 "H" 9 "I" 10 "J" 11 "K" 12 "L" 13 "M" 14 "N" 15 "O" 16 "P" 17 "Q" 18 "R" 19 "S" 20 "T" 21 "U" 22 "V" 23 "W" 24 "X" 25 "Y" 26 "Z" 27 "AA" 28 "AB" 29 "AC" 30 "AD" 31 "AE" 32 "AF" 33 "AG" 34 "AH" 35 "AI" 36 "AJ" 37 "AK" 38 "AL" 39 "AM" 40 "AN" 41 "AO" 42 "AP" 43 "AQ" 44 "AR" 45 "AS" 46 "AT" 47 "AU" 48 "AV" 49 "AW" 50 "AX" , modify
label values loc_num loc_label
decode loc_num, g(loc)

* 20 years
expand 20
bysort loc: gen year = _n
* 15 entities
expand 15
bysort loc year: gen ent_num = _n

label define ent_label 1 "CR AG" 2 "DS BH" 3 "ET CI" 4 "FU DJ" 5 "GV EK" 6 "HW FL" 7 "IX GM" 8 "JY HN" 9 "KZ IO" 10 "LAA JP" 11 "MAB KQ" 12 "NAC LR" 13 "OAD MS" 14 "PAE NT" 15 "QAF OU" , modify
label values ent_num ent_label

decode ent_num, g(entity)

* randomly assign to missing some entitites in some years in some locations
* integers 0 or 1

gen u = floor(2*runiform())
replace entity = "" if u
drop u

******************
* based on Newson, 2012. From resultssets to resultstables in Stata.  The Stata Journal 12 (2): 191-213.
******************

* requires SSC install of:
* rtfutil
* sdecode
* chardef
* xrewide
* listtab

keep ent_num loc year entity
* strip ent_num of label
label values ent_num
sdecode ent_num, gen(row)

qui levelsof loc, l(locs)
local count_locs: word count of `locs'
di "Number of locations: `count_locs'"

* workaround due to unexplainable one more element in locs; see problem 4. above
local end = `count_locs' - 1

* enclose the elements of the local locs with ", so that
* the growlabels() option of ingap can read and apply them; but still not working, see problem 3. above
local first: word 1 of `locs'
local locations `""`first'""'

forvalues k = 2/`end' {
local add: word `k' of `locs'
local add_q `""`add'""'
local locations `"`locations' `add_q'"'
* close k loop
}
*
* code below taken from Newson, 2012 p.210-211 and modified
tempname rtfb1
rtfopen `rtfb1' using "rtfdemo1.rtf", replace margins(1000 1000 1000 1000) paper(a4land)
capture noisily {
chardef row, char(varname) pref("\qr{\i ") suff("}") val("Location/Entity")
xrewide entity, i(loc row) j(year) cjlab(loc) pjlab("\ql{\i ") sjlab("}")
* not completely clear on the cjlab option
bysort loc : ingap, row(row) growlabels("`locations'") neword(rowseq)
chardef entity*, char(varname2) pref("\qr{\i ") suff("}") val("1995" "1996" "1997" "1998" "1999" "2000" "2001" "2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" "2013" "2014" )
rtfrstyle row entity*, cwidths(700 1500) local(b d e)
listtab_vars row entity* , sub(char varname) b(`b') d(`d') e(`e') local(h1)
listtab_vars row entity* , sub(char varname2) b(`b') d(`d') e(`e') local(h2)
file write `rtfb1' "{\pard\b" " Entities appearing in locations by year" "\par}" _n
listtab row entity*, handle(`rtfb1') b(`b') d(`d') e(`e') head("`h2'" "`h1'")
}
rtfclose `rtfb1'

[Roger Newson]

Hello Maria. I think one of your problems is that you are using the levelsof command and hoping that the lo() option will specify a macro containing the labels of the numeric values, instead of the values themselves. If you wanted the lo() option of levelsof to contain value labels instead of values, then you should use Ben Jann's labelsof command, which you can download from SSC, instead of using levelsof, which only lists the values, not their labels.

You seem also to be confused about the cjlabel() option of xrewide, which specifies a characteristic to contain the j-value labels. The vjlabel() option of xrewide specifies the variables for which this characteristic is to be given values.

I hope this helps.

Best wishes

Roger

[Maria Boutchkova]

Thank you, Roger!

I have resolved almost all issues except #1 in my original request for help, i.e. the strings I need to populate the output table do not appear properly. They have spaces and the part before the space gets truncated. I have shortened my example code, so one can run and see the resulting doc file table and how the strings in the table are truncated.

I was applying -levelsof to a string variable and it was collecting what I needed but I wasn't able to pass the contents of the macro on to the growlabels() option of ingap. I have now redone it with gapexpression() instead and it works fine. The curious behavior when counting the number of elements in the macro produced by levelsof still remains, but I shall post a separate question about it.

The misalignment of some rows was because I had not added the RTF prefixes and suffixes everywhere; I do that now with another -sdecode command before the -capture noisily bloc.

* 6 locations
capture set obs 6
gen loc_num = _n
* location labels
label define loc_label 1 "A" 2 "B" 3 "C" 4 "D" 5 "E" 6 "F" , modify
label values loc_num loc_label
decode loc_num, g(loc)
* 5 years
expand 5
bysort loc: gen year = _n
* 4 entities
expand 4
bysort loc year: gen ent_num = _n

label define ent_label 1 "CR AG" 2 "DS BH" 3 "ET CI" 4 "FU DJ" , modify
label values ent_num ent_label

decode ent_num, g(entity)

* randomly assign to missing some entitites in some years in some locations
* integers 0 or 1

gen u = floor(2*runiform())
replace entity = "" if u
drop u

* based on Newson, 2012. From resultssets to resultstables in Stata.  The Stata Journal 12 (2): 191-213.
* requires SSC install of: rtfutil; sdecode; chardef; xrewide; listtab

* strip ent_num of label
label values ent_num
* sdecode to add the RTF prefixes and suffixes
sdecode ent_num, gen(row) pref("\qr{") suff("}")
sdecode loc_num, gen(loc_out) pref("\qr{") suff("}")

tempname rtfb1
rtfopen `rtfb1' using "rtfdemo1.rtf", replace margins(1000 1000 1000 1000) paper(a4land)
capture noisily {
xrewide entity, i(loc_out row) j(year) pjlab("\ql{\i ") sjlab("}")
* not completely clear on the cjlab option
bysort loc_out (ent_num): ingap, row(row) grex(loc_out) neword(rowseq)
chardef row, char(varname) pref("\qr{\i ") suff("}") val("Location/Entity")
chardef entity*, char(varname2) pref("\qr{\i ") suff("}") val("1995" "1996" "1997" "1998" "1999" )
rtfrstyle row entity*, cwidths(1000 1500) local(b d e)
listtab_vars row entity* , sub(char varname) b(`b') d(`d') e(`e') local(h1)
listtab_vars row entity* , sub(char varname2) b(`b') d(`d') e(`e') local(h2)
file write `rtfb1' "{\pard\b" " Entities appearing in locations by year" "\par}" _n
listtab row entity*, handle(`rtfb1') b(`b') d(`d') e(`e') head("`h2'" "`h1'")
}
rtfclose `rtfb1'

[Maria Boutchkova]

Further investigation inside the rtf file shows that the code producing the table rows has nothing between intbl and the text string that should be shown in the respective cell. This can be seen by opening the rtf file produced by the code above using a plain text viewer.

{\trowd\trgaph40\trleft-40\cellx1000\cellx2500\cellx4000\cellx5500\cellx70 00\cellx8500\pard\intbl\qr{1}\cell\pard\intblCR AG\cell\pard\intblCR AG\cell\pard\intblCR AG\cell\pard\intbl\cell\pard\intblCR AG\cell\row}

The text string in this row is "CR AG" and being written right after intbl results in the first word being truncated and only the second word showing in the actual table displayed by a word processor.

My proposed workaround is to replace (or generate new) the variables that are to be displayed in the table cells by adding prefix("\qr{\i }") and suffix("}"). Here is the working code for future reference to anyone else getting stuck with -listtab.

* gen data *

* 6 locations
capture set obs 6
gen loc_num = _n
* location labels
label define loc_label 1 "A" 2 "B" 3 "C" 4 "D" 5 "E" 6 "F" , modify
label values loc_num loc_label
decode loc_num, g(loc)
* 5 years
expand 5
bysort loc: gen year = _n
* 4 entities
expand 4
bysort loc year: gen ent_num = _n

label define ent_label 1 "CR AG" 2 "DS BH" 3 "ET CI" 4 "FU DJ" , modify
label values ent_num ent_label

decode ent_num, g(entity)

* randomly assign to missing some entitites in some years in some locations
* integers 0 or 1

gen u = floor(2*runiform())
replace entity = "" if u
drop u

* based on Newson, 2012. From resultssets to resultstables in Stata.  The Stata Journal 12 (2): 191-213.
* requires SSC install of: rtfutil; sdecode; chardef; xrewide; listtab

* strip ent_num of label
label values ent_num
* sdecode to add the RTF prefixes and suffixes
sdecode ent_num, gen(row) pref("\qr{") suff("}")
sdecode loc_num, gen(loc_out) pref("\qr{") suff("}")

tempname rtfb1
rtfopen `rtfb1' using "rtfdemo1.rtf", replace margins(1000 1000 1000 1000) paper(a4land)
capture noisily {
xrewide entity, i(loc_out row) j(year) pjlab("\ql{\i ") sjlab("}")
* replace the entity strings to be surrounded by the proper prefix and suffix
* get the years for loop (or j-values from reshape wide)
local years: char _dta[ReS_jv] 
di "`years'"
foreach y of local years {
gen entity_out`y' = "\qr{" + entity`y' + "}"
}
*
bysort loc_out (ent_num): ingap, row(row) grex(loc_out) neword(rowseq)
chardef row, char(varname) pref("\qr{\i ") suff("}") val("Location/Entity")
chardef entity_out*, char(varname2) pref("\qr{\i ") suff("}") val("1995" "1996" "1997" "1998" "1999" )
rtfrstyle row entity_out*, cwidths(1000 1500) local(b d e)
listtab_vars row entity_out* , sub(char varname) b(`b') d(`d') e(`e') local(h1)
listtab_vars row entity_out* , sub(char varname2) b(`b') d(`d') e(`e') local(h2)
file write `rtfb1' "{\pard\b" " Entities appearing in locations by year" "\par}" _n
listtab row entity_out*, handle(`rtfb1') b(`b') d("\cell\pard\intbl") e(`e') head("`h2'" "`h1'")
}
rtfclose `rtfb1'