Converting monthly data in to yearly for a long period data

[Fadi Ansar]

Hi All,

I would like to have you help in converting monthly data into yearly. I need to calculate the cumulative sum of each variable in each year for each country. My variables take values from 0 to 12(since it is not possible to show all the variables here). I need to have my dataset for example Albania 1990, and Albania 1991 like that.

I am attaching my data here using data.(dont be confused with data as there are many variables for which the value is coming other than 0 )

* Example generated by -dataex-. To install: ssc install dataex
clear
input str23 Country int(ifscode Year) byte Month str2(CCB_T Conservation_T Capital_T Capital_Gen_T Capital_HH_T Capital_Corp_T Capital_FX_T) byte LVR_T str2(LLP_T LCG_T LCG_Gen_T)
"Albania" 914 1990  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1990 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1991 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1992 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1993 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1994 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1995 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1996 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  5 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  6 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  7 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  8 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997  9 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997 10 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997 11 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1997 12 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1998  1 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1998  2 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1998  3 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
"Albania" 914 1998  4 "0" "0" "0" "0" "0" "0" "0" 0 "0" "0" "0"
end

Thanks in advance
Fadi Ansar

[Andrew Musau]

First of all, you need numerical values to perform arithmetic operations. So start by destringing the values. Then you can collapse the data, summing.

foreach var of varlist CCB_T - LCG_Gen_T{
    destring `var', replace
}
collapse (sum) CCB_T - LCG_Gen_T, by(Country ifscode Year)

Res.:

 l, sepby(Country)

     +---------------------------------------------------------------------------------------------------------------------------------------+
     | Country   ifscode   Year   CCB_T   Conser~T   Capi~l_T   Capi~n_T   Capi~H_T   Capi~p_T   Capi~X_T   LVR_T   LLP_T   LCG_T   LCG_Ge~T |
     |---------------------------------------------------------------------------------------------------------------------------------------|
  1. | Albania       914   1990       0          0          0          0          0          0          0       0       0       0          0 |
  2. | Albania       914   1991       0          0          0          0          0          0          0       0       0       0          0 |
  3. | Albania       914   1992       0          0          0          0          0          0          0       0       0       0          0 |
  4. | Albania       914   1993       0          0          0          0          0          0          0       0       0       0          0 |
  5. | Albania       914   1994       0          0          0          0          0          0          0       0       0       0          0 |
  6. | Albania       914   1995       0          0          0          0          0          0          0       0       0       0          0 |
  7. | Albania       914   1996       0          0          0          0          0          0          0       0       0       0          0 |
  8. | Albania       914   1997       0          0          0          0          0          0          0       0       0       0          0 |
  9. | Albania       914   1998       0          0          0          0          0          0          0       0       0       0          0 |
     +---------------------------------------------------------------------------------------------------------------------------------------+