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

[Miles Grogger <mgrogger@gmail.com>]

Thanks Sergiy,

That helped a lot. Now my program runs the code just fine


On Tue, Aug 6, 2013 at 12:16 PM, Sergiy Radyakin <serjradyakin@gmail.com> wrote:
> 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/
*
*   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/