How to find the sum of the values according to a certain varlist that I wish

[Riccardo Lucarno]

Hello everyone,
In my Dataset I have environmental data concerning different countries in different years.

I.e. small sample

(string) (int)
country_year Totcount

USA1995 .1313796
USA1996 .1248367
USA1997 .1275357

ITA1995 .0151587
ITA1996 .0149385
ITA1997 .0147819

DEU1995 .015136
DEU1996 .0265344
DEU1997 .0147217

DNK1995 .0029698
DNK1996 .002934
DNK1997 .0027339

...

What I am looking for, without success so far, is to write a script that allows me to sum the values in the var "Totcount" per year according to certain list of countries that I wish. More specifically, out of this small example I wrote, what I would like to obtain is the sum per year of "Totcount" by an hypothetical list "european countries", in this case, composed by ITA, DEU and DNK, such that I can obtain ITA1995+DEU1995+DNK1995, ITA1996+DEU1996+DNK1996 ITA1997+DEU1997+DNK1997 etc. without considering the USA data.

Thank you very much in advance,
Riccardo

[Carlo Lazzaro]

Riccardo:
do you mean something along the following lines?

  input  str20 country_year Totcount

             country_year   Totcount
  1.  USA1995 .1313796
  2.  USA1996 .1248367
  3.  USA1997 .1275357
  4.  ITA1995 .0151587
  5.  ITA1996 .0149385
  6.  ITA1997 .0147819
  7.  DEU1995 .015136
  8.  DEU1996 .0265344
  9.  DEU1997 .0147217
 10.  DNK1995 .0029698
 11.  DNK1996 .002934
 12.  DNK1997 .0027339
 13. end

. g wanted=substr(country_year,1,3)

. g watershade=1 if wanted!="USA"

. replace watershade=0 if watershade==.

. egen desired=sum(Totcount) if watershade==1




. list

     +----------------------------------------------------+
     | countr~r   Totcount   wanted   waters~e    desired |
     |----------------------------------------------------|
  1. |  USA1995   .1313796      USA          0          . |
  2. |  USA1996   .1248367      USA          0          . |
  3. |  USA1997   .1275357      USA          0          . |
  4. |  ITA1995   .0151587      ITA          1   .1099089 |
  5. |  ITA1996   .0149385      ITA          1   .1099089 |
     |----------------------------------------------------|
  6. |  ITA1997   .0147819      ITA          1   .1099089 |
  7. |  DEU1995    .015136      DEU          1   .1099089 |
  8. |  DEU1996   .0265344      DEU          1   .1099089 |
  9. |  DEU1997   .0147217      DEU          1   .1099089 |
 10. |  DNK1995   .0029698      DNK          1   .1099089 |
     |----------------------------------------------------|
 11. |  DNK1996    .002934      DNK          1   .1099089 |
 12. |  DNK1997   .0027339      DNK          1   .1099089 |
     +----------------------------------------------------+

.

[Nick Cox]

This overlaps with Carlo Lazzaro's helpful answer but gives separate results for each year.

gen country = substr(country_year, 1, 3)
gen year = real(substr(country_year, 4, .))

egen wanted = total(totcount * inlist(country, "DEU", "ITA", "DNK")), by(year)

[Riccardo Lucarno]

Thank you Carlo, yes it is very close to that! But what I am really looking for is to apply that sum distinguishing also the result for each year. So that, in this case there will be three different results one for the year 1995, on for the year 1996 and 1997. I.e. The first result = to (ITA1995+DEU1995+DNK1995) the second one = to (ITA1996+DEU1996+DNK1996) etc. and not the sum of all the ones different from USA

[Riccardo Lucarno]

Thank you Nic! We posted at the same time