How to use loop to compute total of observations in the same group across alphabet variables

[Giang Dao]

I want to create columns that sum up values along each column of the table according to their Invested type (EUR/CHF/USD/...). I was about to use the following code:

bysort id Invested: egen float Year2001 = total(G)
order Year2001, after(G)

then I supposed to see a column "Year2001" with many dublicated values, I would remove these dublicate values:

sort Invested Year2001
by Invested Year2001:  gen dup = cond(_N==1,0,_n)
drop if dup>1

to have a column with expected values.

But the code just gave me a column just exactly the same as column G. I do not know why and how to fix it.

Also, I tried to use a loop to do this procedure for the variables H, I, J,... but I refered to some threads and it seems to me that I only can do if my variables are from A, B, C,...

Many thanks for your help.

I hope my pic attached is fine.

[Giang Dao]

I just figured out how to fix total(). Many thanks for the loop.

[Nick Cox]

Please read FAQ Advice #12 on why screenshots are less helpful than you hope. Here I can read the image but I don't see any variable Year2001

That said, there is a general question here. I would not particularly recommend removing all but one duplicate. For example, that could be awkward if you wanted to use a total in further calculations and you needed the same total in a group of observations.

I would suggest rather using the egen function tag() which is designed for this purpose. Here is a small example:

. sysuse auto, clear
(1978 Automobile Data)

. egen totalwt = total(weight), by(rep78)

. egen tag = tag(rep78)

. list rep78 totalwt if tag

     +-----------------+
     | rep78   totalwt |
     |-----------------|
  1. |     3     98970 |
  5. |     4     51660 |
 12. |     2     26830 |
 20. |     5     25550 |
 40. |     1      6200 |
     +-----------------+

The effect of tag() is to tag just one of several observations identical on a particular variable. See also the help for egen. In this example, sorting would be a good idea but that's a different point.

[Giang Dao]

Thank you very much. I just read FAQ#12.
Tag helps. I don't have to use dublicate anymore. Thanks a lot.

Just still about the loop. I am trying to rename all alphabets into Year#2001/2012. The following code does not work. Stata said: k not found, so I am stuck here.

display `"`c(ALPHA)'"'
forval j = 2001/2012 {
    foreach k in `c(ALPHA)' {
    if k>="B" {egen Year`j'= total(`k')
        }
        }    
        }