putexcel error

[Mark Davis]

I have a for loop to put variable names and variable labels in A1 and B1 cells respectively. For some reason about 1% of my variables return an error.

foreach var in `varlist'{
    local varlabel : variable label `var'
    putexcel A1=("`var'") B1=("`varlabel'") using "tabulations2 `c(current_date)'", modify sheet("`var'")
}

the error is

errprintf(): 3001 incorrect number of arguments
putexcel_check_paren(): - function returned error
putexcel_is_exp(): - function returned error
putexcel_get_exp_info(): - function returned error
putexcel_add_cellexplist(): - function returned error
putexcel_parse_cellexplist(): - function returned error
putexcel_parse_syntax(): - function returned error
putexcel(): - function returned error
<istmt>: - function returned error

The variables causing the errors have open parentheses, but no closed parentheses. THis is happening because I am importing data from an SPSS file and STATA's variable labels are capped at 80 characters.

I have two ideas on how I may be able to solve the problem. Ideally I would love to increase stata's variable label character limit. I think adding a closed parentheses to all the strings might fix it, but it also might cause new errors. I have had trouble using the char() function. I think it is due to not understanding which kind of quotes to put it in.

[Joseph Coveney]

That behavior looks like a bug, and I recommend reporting it to StataCorp. In the meantime, you can insert the line

if strpos("`varlabel'", "(") & !strpos("`varlabel'", ")") local varlabel `varlabel')

in between the local varlabel and the putexcel lines in order to make sure that the truncated variable labels have both open and close parentheses.

I had run into problems with Stata's 80-character limitation for variable labels in the past (importing SAS datasets), and thought that it had lengthened its maximum since then. Apparently not. Nevertheless, if you're using Stat/Transfer, you could recover the full text for inserting into Excel by converting the SPSS dataset into, say, ASCII/Text--Delimited (S/T Schema). You could then read the schema file (which contains the variable labels) into Stata as a text file and clean it up with the aid of split, leaving two string variables, one containing the name of the variable and the other containing the variable label. Then proceed to putexcel rowwise.

[Mark Davis]

Thanks. I tried the code as follows

foreach var in `somevars2'{
    local varlabel : variable label `var'
    if strpos("`varlabel'", "(") & !strpos("`varlabel'", ")") local varlabel `varlabel')
    putexcel A1=("`var'") B1=("`varlabel'") using "tabulations2 `c(current_date)'", modify sheet("`var'")
}

I got the same error. If i understand this code correctly if varlabel has ( and not ) then varlabel becomes varlabel, but shouldn't it become varlabel+")" or `varlabel'+char(41)?

I have some strings with open two open parens and one closed paren. Is there a way to count the number of "(" and the number of ")" and add the difference to the end of varlabel?


I used usespss to import the data because it's free.

[Nick Cox]

Joseph's suggestion is just to add a right parenthesis by concatenation and looks legal to me. Your suggestions won't work without adding an equals sign to force evaluation.

Counting substrings was discussed in http://www.stata-journal.com/article...article=dm0056

[Clyde Schechter]

Following on Joseph Coveney's suggestion, if you are using Stat/Transfer to port from SPSS to Stata, you don't have to go through the rigamarole of laundering the labels through an ASCII/Text file. Recent versions of Stat/Transfer record the full content of variable labels in the Stata data set as notes when they are too long to be Stata variable labels. You can access those notes through the macro extended functions for characteristics, e.g.

foreach v of varlist [whatever] {
    local fulllabel_`v': char `v'[note1]
}

[Mark Davis]

The code does work for all variables but one. the one causing me problems has two open parens and one closed. I can live without that label.

Thanks everyone for your help.

[Joseph Coveney]

Recent versions of Stat/Transfer record the full content of variable labels in the Stata data set as notes when they are too long to be Stata variable labels.

Thanks for pointing that out, Clyde. I wasn't aware of that handy feature of Stat/Transfer. Perhaps a longer variable label limit ought to be posted on the Stata 14 Wishlist thread . . .