Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to sum over rows for all variables?

    So my data looks like this.
    y1999q1 y1999q2 y1999q3 y1999q4
    US_a
    US_b
    US_c
    CH_a
    CH_b
    CH_c

    I want to create observations (US and CH) so that they are the sum of three observations. Intuitively, I want to make US = US_a + US_b + US_c. How do I do this?

    The actual dataset is extremely large and with many different ids for the observations.

  • #2
    Untested pseudo code follows, because you have not provided a sample of your data with -dataex-.
    Try

    Code:
    set obs `=_N+2'
    
    foreach var of varlist  y1999q1    y1999q2    y1999q3    y1999q4 {
    
    summ `var' if substr(id,1,2)="US"
    
    replace `var' = r(sum) in -2
    
    summ `var' if substr(id,1,2)="CH"
    
    replace `var' = r(sum) in l
    
    }

    Comment


    • #3
      Amazing! This helped out a lot. Thank you!

      Comment


      • #4
        Joro Kolev means (for example)

        Code:
         
          substr(id,1,2) == "US"
        Otherwise, two helpful commands are reshape long and collapse.

        Code:
        * Example generated by -dataex-. To install: ssc install dataex
        clear
        input str4 id byte(y1999q1 y1999q2 y1999q3 y1999q4)
        "US_a" 42 42 42 42
        "US_b" 42 42 42 42
        "US_c" 42 42 42 42
        "CH_a" 42 42 42 42
        "CH_b" 42 42 42 42
        "CH_c" 42 42 42 42
        end
        
        . reshape long y, i(id) j(date) string
        (note: j = 1999q1 1999q2 1999q3 1999q4)
        
        Data                               wide   ->   long
        -----------------------------------------------------------------------------
        Number of obs.                        6   ->      24
        Number of variables                   5   ->       3
        j variable (4 values)                     ->   date
        xij variables:
                    y1999q1 y1999q2 ... y1999q4   ->   y
        -----------------------------------------------------------------------------
        
        
        . gen ndate = quarterly(date, "YQ")
        
        . format ndate %tq
        
        . gen where = substr(id, 1, length(id) - 2)
        
        . l
        
             +-------------------------------------+
             |   id     date    y    ndate   where |
             |-------------------------------------|
          1. | CH_a   1999q1   42   1999q1      CH |
          2. | CH_a   1999q2   42   1999q2      CH |
          3. | CH_a   1999q3   42   1999q3      CH |
          4. | CH_a   1999q4   42   1999q4      CH |
          5. | CH_b   1999q1   42   1999q1      CH |
             |-------------------------------------|
          6. | CH_b   1999q2   42   1999q2      CH |
          7. | CH_b   1999q3   42   1999q3      CH |
          8. | CH_b   1999q4   42   1999q4      CH |
          9. | CH_c   1999q1   42   1999q1      CH |
         10. | CH_c   1999q2   42   1999q2      CH |
             |-------------------------------------|
         11. | CH_c   1999q3   42   1999q3      CH |
         12. | CH_c   1999q4   42   1999q4      CH |
         13. | US_a   1999q1   42   1999q1      US |
         14. | US_a   1999q2   42   1999q2      US |
         15. | US_a   1999q3   42   1999q3      US |
             |-------------------------------------|
         16. | US_a   1999q4   42   1999q4      US |
         17. | US_b   1999q1   42   1999q1      US |
         18. | US_b   1999q2   42   1999q2      US |
         19. | US_b   1999q3   42   1999q3      US |
         20. | US_b   1999q4   42   1999q4      US |
             |-------------------------------------|
         21. | US_c   1999q1   42   1999q1      US |
         22. | US_c   1999q2   42   1999q2      US |
         23. | US_c   1999q3   42   1999q3      US |
         24. | US_c   1999q4   42   1999q4      US |
             +-------------------------------------+
        
        . collapse (sum) y, by(where ndate)
        
        .. list, sepby(where)
        
             +----------------------+
             |  ndate   where     y |
             |----------------------|
          1. | 1999q1      CH   126 |
          2. | 1999q2      CH   126 |
          3. | 1999q3      CH   126 |
          4. | 1999q4      CH   126 |
             |----------------------|
          5. | 1999q1      US   126 |
          6. | 1999q2      US   126 |
          7. | 1999q3      US   126 |
          8. | 1999q4      US   126 |
             +----------------------+
        .

        Comment

        Working...
        X