Writing valuelabels to Excel using gettoken in a loop

[Emil Alnor]

I am trying to write the results of a survey to Excel. I need to report a lot of questions, which have the same answer categories (but are not part of a battery). I want to put the answer categories in text into Excel. This could be done by

putexcel set test, sheet(test)
putexcel a2 = "To a high extent"
putexcel a3 = "To some extent"
putexcel a4 = "To little extent"
putexcel a5 = "To no extent"

But as i need to do it a lot of times (and also to understand -gettoken-) it would be convenient to do in a loop using -gettoken-. My intution is

putexcel set test, sheet(test)
local extent To a high extent,To some extent,To little extent,To no extent
forval x = 2/5{
gettoken label extent : extent, parse(",")
putexcel a`x' = "`label'"
}

But this returns " is not a valid command name". So how do I put the valuelabels in Excel using gettoken?

[Hemanshu Kumar]

I cannot replicate your error. I get no error; however, I also don't get the result you want. This is because gettoken does not remove the parse character from the macro. So your cells end up being

To a high extent
,
To some extent
,

To avoid the problem, you can just do:

putexcel set test, sheet(test) modify
local extent To a high extent,To some extent,To little extent,To no extent
forval x = 2/5{
    gettoken label extent : extent, parse(",")
    putexcel a`x' = "`label'"
    gettoken label extent : extent, parse(",")
}

[Emil Alnor]

putexcel set test, sheet(test) modify
local extent To a high extent,To some extent,To little extent,To no extent
forval x = 2/5{
gettoken label extent : extent, parse(",")
putexcel a`x' = "`label'"
gettoken label extent : extent, parse(",")
}

Thanks Hemanshu Kumar this solved my problem. I'm suprised, though, that I would have to write the same -gettoken- two times in order to remove the parse character (why is not an option instead). But maybe there is an easier way to solve my problem than to define my labels in local and seperating them by "," in the first place. I know from "Speaking Stata: Loops in parallel" from the Stata Journal, that another way around is using "`'" to seperate the labels in the local, but I'm not sure thats is more effective in terms of characters used and readability of code in my case.

[Hemanshu Kumar]

Yes, instead of using gettoken, I would have just written this code as:

local extent `" "To a high extent" "To some extent" "To little extent" "To no extent" "'

local i = 2
putexcel set test, sheet(test) modify
foreach lab of local extent {
    putexcel a`i' = `"`lab'"'
    local ++i
}

[Nick Cox]

I think the answer is that gettoken is deliberately general and written to include the possibility that what you parse on may be informative. Spaces that separate words are not deemed to be informative otherwise, but other punctuation may be informative. I've sometimes wanted an option to say that the separators are to be discarded, but gettoken gives you them any way.

A common example where commas are needed, or at least helpful, is when you are parsing user syntax and what is downstream of you are one or more options or suboptions of a command.

[daniel klein]

Depending on the details I would call uselabel, then export the resulting dataset. But the question appears to be -- at least in part -- on learning to use gettoken.

[Nick Cox]

See also

. mata
------------------------------------------------- mata (type end to exit) ---------------
: ustrsplit("To a high extent,To some extent,To little extent,To no extent", ",")
                      1                  2                  3                  4
    +-----------------------------------------------------------------------------+
  1 |  To a high extent     To some extent   To little extent       To no extent  |
    +-----------------------------------------------------------------------------+