Running sum for several variables using foreach

[Filip De Beule]

I cannot get the foreach command to work to calculate running sums for several variables. Any help would be highly appreciated.

I have data for many companies from 1980 through 2022 and 249 countries (abbreviated using ISO2 codes). I want to calculate the running sums for every country. The command bysort company (year): gen sum_AF = sum(AF) and so on, for each country, works fine. But I cannot get the command to work using the foreach variable. What am I doing wrong?


vl create countrylist = (AF-ZW)

foreach var in countrylist {
bysort company (year): gen cum_'var' = sum('var')
}

. dataex company year AF AX AL ZW

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 company float(year AF AX AL ZW)
"SAIC" 1980 0 0 0 0
"SAIC" 1981 0 0 0 0
"SAIC" 1982 0 0 0 0
"SAIC" 1983 0 0 0 0
"SAIC" 1984 0 0 0 0
"SAIC" 1985 0 0 0 0
"SAIC" 1986 0 0 0 0
"SAIC" 1987 0 0 0 0
"SAIC" 1988 0 0 0 0
"SAIC" 1989 0 0 0 0
"SAIC" 1990 0 0 0 0
"SAIC" 1991 0 0 0 0
"SAIC" 1992 0 0 0 0
"SAIC" 1993 0 0 0 0
"SAIC" 1994 0 0 0 0
"SAIC" 1995 0 0 0 0
"SAIC" 1996 0 0 0 0
"SAIC" 1997 0 0 0 0
"SAIC" 1998 0 0 0 0
"SAIC" 1999 0 0 0 0
"SAIC" 2000 0 0 0 0
"SAIC" 2001 0 0 0 0
"SAIC" 2002 0 0 0 0
"SAIC" 2003 0 0 0 0
"SAIC" 2004 0 0 0 0
"SAIC" 2005 0 0 0 0
"SAIC" 2006 0 0 0 0
"SAIC" 2007 0 0 0 0
"SAIC" 2008 0 0 0 0
"SAIC" 2009 0 0 0 0
"SAIC" 2010 0 0 0 0
"SAIC" 2011 0 0 0 0
"SAIC" 2012 0 0 0 0
"SAIC" 2013 0 0 1 0
"SAIC" 2014 0 0 0 0
"SAIC" 2015 0 0 0 0
"SAIC" 2016 0 0 0 0
"SAIC" 2017 0 0 0 0
"SAIC" 2018 0 0 0 0
"SAIC" 2019 0 0 0 0
"SAIC" 2020 0 0 0 0
"SAIC" 2021 1 0 1 1
"SAIC" 2022 0 0 0 0
end

[Rich Goldstein]

I see two issues: (1) the -vl- command creates a global macro so in your -foreach- command you need to put "$" (without the quotes) directly (i.e., no space) in front of "countrylist"; (2) (this may be a function of copying into Statalist), your command within the foreach block, refers to var using the incorrect symbol at the start - you have "'" and what you need is "`" (i.e., on my keyboard the character you need is in the upper left on the same key as the tilde); I have not checked the actual calculations after making these changes but it does run without error

[Filip De Beule]

Dear Rich,

Thank you very much the quick feedback. This indeed did the trick. I did not know about the need for $ in case of a global macro. And the ' vs. ´ was not a paste error but a keyboard error as I am using an azerty keyboard. The calculations seemed to work upon perusal of some variables.

Would you happen to also know how to generate a new dummy variable for each country equal to 1 from the year of first introduction? To give an example, from the few countries I copied in the data-ex, AL has a value of 1 from 2013. So a new variable which is equal to 1 from 2013 but which does not sum across observations.

[Rich Goldstein]

glad it worked

I don't fully understand what you are asking but, I think, the first part should be something like:

bysort country (year): gen first=_n==1

obviously, you should rename "first" to whatever you want

my confusion mainly stems from whether company needs to be part of the command; maybe showing example data with a column for what is "wanted" would help

[Filip De Beule]

What I want is quite similar to the previous request but rather than a running sum, I want a dummy variable taking 1 from first year the AF, AX, AL variables also have a 1. I made the below-mentioned table in Excel to illustrate. I hope it is clear. I tried adapting your suggested code line into the below-mentioned code but this just created a variable with 1 in first year (1980).

foreach var in $countrylist {
bysort company (year): gen first_`var' =_n==1
}

company	year	AF	AX	AL	sum_AF	sum_AX	sum_AL	first_AF	first_AX	first_AL
SAIC	1980	0	0	0	0	0	0	0	0	0
SAIC	1981	0	0	0	0	0	0	0	0	0
SAIC	1982	0	0	0	0	0	0	0	0	0
SAIC	1983	0	0	0	0	0	0	0	0	0
SAIC	1984	0	0	0	0	0	0	0	0	0
SAIC	1985	0	0	0	0	0	0	0	0	0
SAIC	1986	0	0	0	0	0	0	0	0	0
SAIC	1987	0	0	0	0	0	0	0	0	0
SAIC	1988	0	0	0	0	0	0	0	0	0
SAIC	1989	0	0	0	0	0	0	0	0	0
SAIC	1990	0	0	0	0	0	0	0	0	0
SAIC	1991	0	0	0	0	0	0	0	0	0
SAIC	1992	0	0	0	0	0	0	0	0	0
SAIC	1993	0	0	0	0	0	0	0	0	0
SAIC	1994	0	0	0	0	0	0	0	0	0
SAIC	1995	0	0	0	0	0	0	0	0	0
SAIC	1996	0	0	0	0	0	0	0	0	0
SAIC	1997	0	0	0	0	0	0	0	0	0
SAIC	1998	0	0	0	0	0	0	0	0	0
SAIC	1999	0	0	0	0	0	0	0	0	0
SAIC	2000	0	0	0	0	0	0	0	0	0
SAIC	2001	0	0	0	0	0	0	0	0	0
SAIC	2002	0	0	0	0	0	0	0	0	0
SAIC	2003	0	0	0	0	0	0	0	0	0
SAIC	2004	0	0	0	0	0	0	0	0	0
SAIC	2005	0	0	0	0	0	0	0	0	0
SAIC	2006	0	0	0	0	0	0	0	0	0
SAIC	2007	0	0	0	0	0	0	0	0	0
SAIC	2008	0	0	0	0	0	0	0	0	0
SAIC	2009	0	0	0	0	0	0	0	0	0
SAIC	2010	0	0	0	0	0	0	0	0	0
SAIC	2011	0	0	0	0	0	0	0	0	0
SAIC	2012	0	0	0	0	0	0	0	0	0
SAIC	2013	0	0	1	0	0	1	0	0	1
SAIC	2014	0	0	0	0	0	1	0	0	1
SAIC	2015	0	0	0	0	0	1	0	0	1
SAIC	2016	0	0	0	0	0	1	0	0	1
SAIC	2017	0	0	0	0	0	1	0	0	1
SAIC	2018	0	0	0	0	0	1	0	0	1
SAIC	2019	0	0	0	0	0	1	0	0	1
SAIC	2020	0	0	0	0	0	1	0	0	1
SAIC	2021	1	0	1	1	0	2	1	0	1
SAIC	2022	0	0	0	1	0	2	1	0	1