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

[Sergiy Radyakin <serjradyakin@gmail.com>]

Miles,  in your code you first assemble all the file names in one list
(first part where you want a loop), and then you extract each
individual file name from that list (second part where you do your
processing). This is redundant. Organize your list as a simple text
file with filenames and sheetnames, then read from it sequentially.

file1.xls * chapter1
c:\temp\file2.xls * chapter2
D:\Data\myfiles\file3.xls * CHAPTER3
....etc....

use a star (*) which is not valid for a filename character for a
separator between filenames and sheets.

Then see an example from help for file:

                tempname fh
                local linenum = 0
                file open `fh' using `"`using'"', read
                file read `fh' line
                while r(eof)==0 {

                        //here you split the "`line'" using star as a
separator and do your processing
                        // .......

                        file read `fh' line
                }
                file close `fh'

Finally, it seems that all your code is doing is reshaffling the
sheets of Excel files. I don't see any real processing in Stata. That
might be somewhat easier to do in Excel itself, which has a copy sheet
command, see e.g.
http://stackoverflow.com/questions/8439657/copy-an-entire-worksheet-to-a-new-worksheet-in-excel-2010
and plenty of other references. In that case you avoid any
compatibility/transformation/formatting issues arising from pulling
data into Stata and exporting back to Excel.


Best, Sergiy




On Wed, Aug 7, 2013 at 12:56 PM, Miles Grogger <mgrogger@gmail.com> wrote:
> Thanks Nick. This really helped a lot. The loop worked so I think I
> will stick with that for now. Thank you very much again.
>
> Miles
>
> On Wed, Aug 7, 2013 at 10:11 AM, Nick Cox <njcoxstata@gmail.com> wrote:
>> 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/
> *
> *   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/