Re: st: Egen to sum across rows (with an if across rows)

[Nick Cox <njcoxstata@gmail.com>]

You are correct. Wildcards cannot be used in -if- qualifiers (or -if-
commands for that matter).

Your syntax needs fixing in other ways. You use -cat- in the -foreach-
statement but don't  refer to it in the loop. That's not illegal in
itself, but the code couldn't do what you want.

You state different variable names in different places, but the spirit
of what you want seems clear.

Try this:

foreach cat in A B C D {

      gen wardtime`cat' = 0

      qui forval j = 1/157 {
             replace wardtime`cat' = wardtime`cat' + t`j'  if  cat`j' == "`cat'"
      }

}

That's assuming variables -t1-t157- -cat1-cat157-

There is a general review of technique in this territory in

SJ-9-1  pr0046  . . . . . . . . . . . . . . . . . . .  Speaking Stata: Rowwise
        (help rowsort, rowranks if installed) . . . . . . . . . . .  N. J. Cox
        Q1/09   SJ 9(1):137--157
        shows how to exploit functions, egen functions, and Mata
        for working rowwise; rowsort and rowranks are introduced

.pdf at http://www.stata-journal.com/sjpdf.html?articlenum=pr0046

Nick
njcoxstata@gmail.com


On 29 April 2013 08:18, Lucy GELDER <l.gelder@ecu.edu.au> wrote:

> I have a dataset which includes 157 columns of times in hours (t1-t157) and 157 columns of categories, with values A - D (cat1-cat157).
>
> I want to sum across the columns by category, so that I end up with four columns timeA-timeD containing the total times for each category.
>
> I have tried:
>
> foreach cat in A B C D{
>
>         egen wardtimeA= rowtotal(wardtime*) if (wardcat*)=="A"
>
> }
>
> and get the error "wardcat* invalid name". I presume this means I can't use the wild card in the if statement?
>
> Does anyone know of a way I can do this without reshaping to data long.....this is a very large dataset and I would prefer to keep it wide if possible.
*
*   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/