Re: st: Reading a variable from a spreadsheet and saving it as a macro

[Sergiy Radyakin <serjradyakin@gmail.com>]

Thats because you need to put quotes around `shnames'

currently it tries to execute the following statement (after the macro
is expanded)
display chp[1] chp[2] chp[3]
This is syntactically correct statement, and it means: "display the
first value of the variable chp".
Even though there is no syntax problem, the statement is valid only in
presence of a variable chp.
The macro didn't disappear anywhere and is still useable.
Note also that a statement "display 1 2 3" will result in "123" - this
is not what you want, right?

However, based on what you described you INTEND to do is to store the
scalars read from a file to a macro. Storing the chp[1] into a macro
would not do you any good in the absence of the data loaded into
memory. This is called "indirect reference" - instead of storing the
parameter, you store the address where the parameter may be retrieved.
It is a valid and useful programming technique, but the address must
point to some valid content, and content is what you clear in your
program.

There was a thread a while ago on the meaning and use of the '=' sign (equal):
http://www.stata.com/statalist/archive/2013-07/msg01032.html

In your case instead of
local shnames "chp[1] chp[2] chp[3]"
you want
local shnames "`=chp[1]' `=chp[2]' `=chp[3]'"

The resulting macro should survive the clearing of the dataset and
provide you with a list of 3 scalar parameters (that you can extract
with the word function if you want).

See example here:
do http://www.radyakin.org/statalist/2013080601/example.do

Best, Sergiy Radyakin


On Tue, Aug 6, 2013 at 12:11 PM, Miles Grogger <mgrogger@gmail.com> wrote:
> Thanks Sergiy,
>
> I was thinking the exact same thing when the error message showed up.
> It will run this code through line 12 (display `shnames') and then
> crash stating "chp not found".
>
> local indir "/Users/milesgrogger/Dropbox/PAC_Facilities/FullChapters/1995-2011"
> local outdir "/Users/milesgrogger/Desktop"
> cd `outdir'
> import excel chapters.xlsx, firstrow
> reshape long state, i(stcode) j(year)
> local fnames "state[1] state[2] state[3]"
> clear
> import excel sheets.xlsx, firstrow
> reshape long chp, i(stcode) j(year)
> local shnames "chp[1] chp[2] chp[3]"
> clear
> display `shnames'
> display `fnames'
>
> The same thing happens if I substitute "clear" for "drop _all". My
> only hypothesis thus far for the error is that stata is reading
> `fnames' as having the contents "state[1] state[2] state[3]", and
> `shnames' with it's version in turn, rather than reading the contents
> of "state[1...2....3].
>
> Thanks in advance for any additional help,
> Miles
>
> On Tue, Aug 6, 2013 at 10:42 AM, Sergiy Radyakin <serjradyakin@gmail.com> wrote:
>> On Tue, Aug 6, 2013 at 11:17 AM, Miles Grogger <mgrogger@gmail.com> wrote:
>>> Hi all,
>>>
>>
>>> but when I cleared the dataset to make room for the next workbook, the
>>> macros were cleared too.
>>
>> This should not be happening as the following script demonstrates, the
>> locals survive clearing the dataset:
>>
>> version 12.0
>> sysuse auto, clear
>> local a="test"
>> clear all
>> assert `"`a'"'=="test"
>>
>> Perhaps something else is going on?
>>
>> Also note that in Stata 13 there are new commands to interact with
>> Excel. If you have Stata 13, this is what you probably need:
>> http://www.stata.com/manuals13/m-5xl.pdf
>>
>> With that you should be able to have multiple Excel spreadsheets open
>> simultaneously and have random access to individual cells.
>>
>> Best, Sergiy Radyakin
>> *
>> *   For searches and help try:
>> *   http://www.stata.com/help.cgi?search
>> *   http://www.stata.com/support/faqs/resources/statalist-faq/
>> *   http://www.ats.ucla.edu/stat/stata/
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/