Re: st: Creating a loop for placing observations in a macro without vallist

[Nick Cox <njcoxstata@gmail.com>]

I'd suggest looking towards Mata for tools here. I love Stata macros
and use them all the time but in essence they are for string
manipulation, and not so well suited for heaving data around.
Nick
njcoxstata@gmail.com


On 7 August 2013 16:06, Miles Grogger <mgrogger@gmail.com> wrote:
> Thanks Nick I'll test your suggestion and see if it works.
>
> The basis of my project is to take workbooks, located in one folder,
> extract a sheet from them, and then save that sheet into a separate
> folder.
>
> However, I also need to keep track of the filename of the workbooks in
> question as well as the sheets being used, thus I am creating two
> other workbooks; one to file the file names (let's call this
> file_name.xls) and one to file the sheet names (let's call this
> sheet_name.xls). From there, I want to construct a loop (as previously
> mentioned) which will allow for me to collect the data from
> file_name.xls, and the corresponding data from sheet_name.xls, and
> then plug that information into the already written extraction loop to
> extract the sheet and put it in a new folder.
>
> Here is a code to make things a bit clearer:
>
> 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]' `=state[4]'
> `=state[5]' `=state[6]'" /*where I would like a new loop*/
>
> clear
> import excel sheets.xlsx, firstrow
> reshape long chp, i(stcode) j(year)
> local shnames "`=chp[1]' `=chp[2]' `=chp[3]' `=chp[4]' `=chp[5]'
> `=chp[6]'" /*where I would like a new loop*/
>
> clear
> local n_f : word count `fnames'
> local n_sh : word count `shnames'
> assert `n_f' == `n_sh'
> cd `indir'
> forval i=1/`n_f' {
>  local x `: word `i' of `fnames''
>  local y `: word `i' of `shnames''
>  import excel `x', sheet(`y')
>  cd `outdir'
>  export excel `x'
>  clear
>  cd `indir'
> }
>
> Best,
> Miles
>
> On Wed, Aug 7, 2013 at 9:38 AM, Nick Cox <njcoxstata@gmail.com> wrote:
>> -vallist- (SSC) is a user-written program. The help file explains its
>> history, starting out as something I did and as of 2003 being the
>> responsibility of Patrick Joly. Whatever the details, you are asked to
>> explain where user-written programs you refer to come from. However,
>> -vallist- was designed to show the _distinct_ values of a variable and
>> will not usually report them in the order in which they are observed
>> in the data. If values are repeated, that is an extra problem.
>>
>> But you can write a loop easily enough, say
>>
>> forval i = 1/`=_N' {
>>              local macname `macname' `=varname[`i']'
>> }
>>
>> However, there are big reservations here.
>>
>> 1. If the variable is a string, you need to use quotation marks and be
>> careful about spaces, etc.
>>
>> 2. If the variable is numeric with fractional parts, you are likely to
>> lose information.
>>
>> 3. Why are you doing this any way? It sounds a very odd thing to want to do.
>> Nick
>> njcoxstata@gmail.com
>>
>>
>> On 7 August 2013 15:16, Miles Grogger <mgrogger@gmail.com> wrote:
>>> Hi Statalisters,
>>>
>>> I currently have a program that is allowing me to import an excel
>>> file, reshape it, and then place observations in a macro by using the
>>> command:
>>>
>>> local macname "`=varname[1]' `varname[2]'...etc."
>>>
>>> This works fine for a few observations, however I eventually want to
>>> be able to place 544 observations in the macro. Is there a loop I
>>> could use in order to make all of this more automatic?
>>>
>>> Thanks,
>>> Miles
>>> *
>>> *   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/