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

[Miles Grogger <mgrogger@gmail.com>]

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/