joining the same observations with particular criterion

[Gail Hally]

I currently have a dataset that contains multiple of the same values for a variable (industry_code) in different areas (different values of the variable area_flips) and for different codes (agglvl_code) with quarterly data. I want to narrow this to achieve just the 4 quarterly values for each industry code (so a total of all the areas and agglvl_codes for a single industry_code but whilst keeping quarterly observations and other variables for each quarter).

[David Benson]

Hi Gail and welcome to Statalist!

1) So it would be *really* helpful if you could share 30-40 obs of your data using Stata's dataex command (SSC install dataex). If you need help using dataex, I created a Youtube tutorial here. (I made it too long--feel free to watch at 2x speed, and you may only need the first 6 minutes)

2) Based on your description it sounds like you will either use egen, total() or collapse

You might find the following posts helpful: See here, here, here, and here

* Some toy data
dataex firm qtr_string country store product sales  // data shared via -dataex-. To install: ssc install dataex
clear
input str5 firm str6(qtr_string country) byte store str7 product long sales
"Apple" "2011q1" "Canada" 1 "Macbook" 54017
"Apple" "2011q1" "Canada" 1 "iPad"    98388
"Apple" "2011q1" "Canada" 1 "iPhone"  46391
"Apple" "2011q1" "Canada" 2 "Macbook" 55803
"Apple" "2011q1" "Canada" 2 "iPad"    76298
"Apple" "2011q1" "Canada" 2 "iPhone"  27000
"Apple" "2011q1" "Canada" 3 "Macbook" 40805
"Apple" "2011q1" "US"     1 "Macbook" 64737
"Apple" "2011q1" "US"     1 "iPad"    60913
"Apple" "2011q1" "US"     1 "iPhone"  47897
"Apple" "2011q1" "US"     2 "Macbook" 33789
"Apple" "2011q1" "US"     2 "iPad"    55706
"Apple" "2011q1" "US"     2 "iPhone"  52466
"Apple" "2011q1" "US"     3 "Macbook" 71853
end

------------------ copy up to and including the previous line ------------------

* Converting string date to Stata date
gen qtr = quarterly( qtr_string, "YQ", 2020)  // see "help datetime translation"
gen year = yofd(dofq( qtr ))  // getting year from quarterly date
format qtr %tq
format %tq qtr
order qtr year, after( qtr_string)

sort firm qtr country store product

. list, sepby( firm country) noobs abbrev(12)

  +-------------------------------------------------------------------------+
  |  firm   qtr_string      qtr   year   country   store   product    sales |
  |-------------------------------------------------------------------------|
  | Apple       2011q1   2011q1   2011    Canada       1   Macbook   54,017 |
  | Apple       2011q1   2011q1   2011    Canada       1      iPad   98,388 |
  | Apple       2011q1   2011q1   2011    Canada       1    iPhone   46,391 |
  | Apple       2011q1   2011q1   2011    Canada       2   Macbook   55,803 |
  | Apple       2011q1   2011q1   2011    Canada       2      iPad   76,298 |
  | Apple       2011q1   2011q1   2011    Canada       2    iPhone   27,000 |
  | Apple       2011q1   2011q1   2011    Canada       3   Macbook   40,805 |
  |-------------------------------------------------------------------------|
  | Apple       2011q1   2011q1   2011        US       1   Macbook   64,737 |
  | Apple       2011q1   2011q1   2011        US       1      iPad   60,913 |
  | Apple       2011q1   2011q1   2011        US       1    iPhone   47,897 |
  | Apple       2011q1   2011q1   2011        US       2   Macbook   33,789 |
  | Apple       2011q1   2011q1   2011        US       2      iPad   55,706 |
  | Apple       2011q1   2011q1   2011        US       2    iPhone   52,466 |
  | Apple       2011q1   2011q1   2011        US       3   Macbook   71,853 |
  +-------------------------------------------------------------------------+

* Calculating subtotals (allows me to check work below)
table country product, c(sum sales) format(%10.0fc) row col

----------------------------------------------------------
          |                    product                    
  country |    Macbook        iPad      iPhone       Total
----------+-----------------------------------------------
   Canada |    150,625     174,686      73,391     398,702
       US |    170,379     116,619     100,363     387,361
          |
    Total |    321,004     291,305     173,754     786,063
----------------------------------------------------------


. table country store, c(sum sales) format(%10.0fc) row col

----------------------------------------------------------
          |                     store                    
  country |          1           2           3       Total
----------+-----------------------------------------------
   Canada |    198,796     159,101      40,805     398,702
       US |    173,547     141,961      71,853     387,361
          |
    Total |    372,343     301,062     112,658     786,063
----------------------------------------------------------


. table country product store, c(sum sales) format(%10.0fc) row col

---------------------------------------------------------------------------------------------------------------------------------------------
        |                                                          store and product                                                        
        | -------------------- 1 -------------------- -------------------- 2 -------------------- -------------------- 3 --------------------
country |    Macbook       iPad     iPhone      Total    Macbook       iPad     iPhone      Total    Macbook       iPad     iPhone      Total
--------+------------------------------------------------------------------------------------------------------------------------------------
 Canada |     54,017     98,388     46,391    198,796     55,803     76,298     27,000    159,101     40,805                           40,805
     US |     64,737     60,913     47,897    173,547     33,789     55,706     52,466    141,961     71,853                           71,853
        |
  Total |    118,754    159,301     94,288    372,343     89,592    132,004     79,466    301,062    112,658                          112,658
---------------------------------------------------------------------------------------------------------------------------------------------

* Calculating subtotals
egen country_total = total(sales), by( firm qtr country)
egen country_store_total = total(sales), by( firm qtr country store )
format country_total country_store_total %10.0fc

. list firm qtr country store product sales country_total country_store_total, sepby(firm country store) noobs abbrev(21)

  +-------------------------------------------------------------------------------------------+
  |  firm      qtr   country   store   product    sales   country_total   country_store_total |
  |-------------------------------------------------------------------------------------------|
  | Apple   2011q1    Canada       1   Macbook   54,017         398,702               198,796 |
  | Apple   2011q1    Canada       1      iPad   98,388         398,702               198,796 |
  | Apple   2011q1    Canada       1    iPhone   46,391         398,702               198,796 |
  |-------------------------------------------------------------------------------------------|
  | Apple   2011q1    Canada       2   Macbook   55,803         398,702               159,101 |
  | Apple   2011q1    Canada       2      iPad   76,298         398,702               159,101 |
  | Apple   2011q1    Canada       2    iPhone   27,000         398,702               159,101 |
  |-------------------------------------------------------------------------------------------|
  | Apple   2011q1    Canada       3   Macbook   40,805         398,702                40,805 |
  |-------------------------------------------------------------------------------------------|
  | Apple   2011q1        US       1   Macbook   64,737         387,361               173,547 |
  | Apple   2011q1        US       1      iPad   60,913         387,361               173,547 |
  | Apple   2011q1        US       1    iPhone   47,897         387,361               173,547 |
  |-------------------------------------------------------------------------------------------|
  | Apple   2011q1        US       2   Macbook   33,789         387,361               141,961 |
  | Apple   2011q1        US       2      iPad   55,706         387,361               141,961 |
  | Apple   2011q1        US       2    iPhone   52,466         387,361               141,961 |
  |-------------------------------------------------------------------------------------------|
  | Apple   2011q1        US       3   Macbook   71,853         387,361                71,853 |
  +-------------------------------------------------------------------------------------------+