Conditional summing

[Rafal Krol]

Hi,
I am working with panel data and as I am not the greatest STATA programmer, I would like to kindly ask you for help.
The data below consists of:
- Date - monthly basis
- Id - number of firm (sample of 90000 different firms)
- As - assets at the end of the month
- Re - reason for disappearing from the dataset (blank if it has survived, M-merged, L-liquidated)
- Me - ID of the company that it has merged with (blank or a value)

Date ID As Re Me
13604 1 12.081 "M" 8441
13634 1 20.54 "M" 8441
13664 1 26.157 "M" 8441
13695 1 34.609 "M" 8441
13726 1 42.49 "M" 8441
13755 1 46.629 "M" 8441
13787 1 57.9 "M" 8441
13818 1 69.717 "M" 8441
13846 1 75.033 "M" 8441
13879 1 81 "M" 8441
13909 1 87.025 "M" 8441
13937 1 92.097 "M" 8441
13969 1 100.496 "M" 8441
13999 1 112.68 "M" 8441
14028 1 122.967 "M" 8441
14060 1 138.424 "M" 8441
14091 1 149.418 "M" 8441
14122 1 166.043 "M" 8441
14152 1 179.6 "M" 8441
14182 1 188.5 "M" 8441
14213 1 195.4 "M" 8441
14244 1 196.7 "M" 8441
14273 1 206.6 "M" 8441
14301 1 209.7 "M" 8441
14334 1 217.2 "M" 8441
14364 1 211.1 "M" 8441
14392 1 216.9 "M" 8441
14425 1 213.1 "M" 8441
14455 1 212.2 "M" 8441
14487 1 209.2 "M" 8441
14517 1 208.4 "M" 8441
14546 1 206.7 "M" 8441
14578 1 206.1 "M" 8441
14609 1 200.1 "M" 8441
14640 1 190.8 "M" 8441
14669 1 186.1 "M" 8441
14700 1 185.9 "M" 8441
14728 1 181.9 "M" 8441
14761 1 179.7 "M" 8441
14791 1 182.1 "M" 8441
14822 1 183.7 "M" 8441
13604 8441 583.547 "" .
13634 8441 587.014 "" .
13664 8441 591.688 "" .
13695 8441 602.7 "" .
13726 8441 626.6 "" .
13755 8441 621.969 "" .
13787 8441 640.1 "" .
13818 8441 657.407 "" .
13846 8441 682.826 "" .
13879 8441 695.6 "" .
13909 8441 721.005 "" .
13937 8441 728.309 "" .
13969 8441 739.741 "" .
13999 8441 745.048 "" .
14028 8441 752.777 "" .
14060 8441 774.328 "" .
14091 8441 786.819 "" .
14122 8441 789.716 "" .
14152 8441 783.7 "" .
14182 8441 794.4 "" .
14213 8441 802.9 "" .
14244 8441 807.9 "" .
14273 8441 787.3 "" .
14301 8441 769.6 "" .
14334 8441 793.5 "" .
14364 8441 791.6 "" .
14392 8441 776.2 "" .
14425 8441 758.7 "" .
14455 8441 762.3 "" .
14487 8441 749.2 "" .
14517 8441 748.6 "" .
14546 8441 736.9 "" .
14578 8441 727.8 "" .
14609 8441 713 "" .
14640 8441 689 "" .
14669 8441 687.9 "" .
14700 8441 686.5 "" .
14728 8441 678 "" .
14761 8441 660.6 "" .
14791 8441 666.7 "" .
14822 8441 648.7 "" .
14853 8441 639.6 "" .
14882 8441 639.4 "" .










My goal is to add the assets of companies that have M value in the "Reason" variable to the assets of company indicated in "Merge" variable at each date_t - "Date" variable (march to march, april to april etc.). I have completely no idea how to perform this, as I have never done anything similar before. I would like to use "replace" function but I don't know how to start. I don't know how to let STATA know that if Reason==M "read" merge value and add its assets to the "read" Merge number, for each same month.


Any help/hint/remark will be valuable!

Best,
Rafał

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input int(date id) float as str3 re int me
13604    1  12.081 "M" 8441
13634    1   20.54 "M" 8441
13664    1  26.157 "M" 8441
13695    1  34.609 "M" 8441
13726    1   42.49 "M" 8441
13755    1  46.629 "M" 8441
13787    1    57.9 "M" 8441
13818    1  69.717 "M" 8441
13846    1  75.033 "M" 8441
13879    1      81 "M" 8441
13909    1  87.025 "M" 8441
13937    1  92.097 "M" 8441
13969    1 100.496 "M" 8441
13999    1  112.68 "M" 8441
14028    1 122.967 "M" 8441
14060    1 138.424 "M" 8441
14091    1 149.418 "M" 8441
14122    1 166.043 "M" 8441
14152    1   179.6 "M" 8441
14182    1   188.5 "M" 8441
14213    1   195.4 "M" 8441
14244    1   196.7 "M" 8441
14273    1   206.6 "M" 8441
14301    1   209.7 "M" 8441
14334    1   217.2 "M" 8441
14364    1   211.1 "M" 8441
14392    1   216.9 "M" 8441
14425    1   213.1 "M" 8441
14455    1   212.2 "M" 8441
14487    1   209.2 "M" 8441
14517    1   208.4 "M" 8441
14546    1   206.7 "M" 8441
14578    1   206.1 "M" 8441
14609    1   200.1 "M" 8441
14640    1   190.8 "M" 8441
14669    1   186.1 "M" 8441
14700    1   185.9 "M" 8441
14728    1   181.9 "M" 8441
14761    1   179.7 "M" 8441
14791    1   182.1 "M" 8441
14822    1   183.7 "M" 8441
13604 8441 583.547 ""     .
13634 8441 587.014 ""     .
13664 8441 591.688 ""     .
13695 8441   602.7 ""     .
13726 8441   626.6 ""     .
13755 8441 621.969 ""     .
13787 8441   640.1 ""     .
13818 8441 657.407 ""     .
13846 8441 682.826 ""     .
13879 8441   695.6 ""     .
13909 8441 721.005 ""     .
13937 8441 728.309 ""     .
13969 8441 739.741 ""     .
13999 8441 745.048 ""     .
14028 8441 752.777 ""     .
14060 8441 774.328 ""     .
14091 8441 786.819 ""     .
14122 8441 789.716 ""     .
14152 8441   783.7 ""     .
14182 8441   794.4 ""     .
14213 8441   802.9 ""     .
14244 8441   807.9 ""     .
14273 8441   787.3 ""     .
14301 8441   769.6 ""     .
14334 8441   793.5 ""     .
14364 8441   791.6 ""     .
14392 8441   776.2 ""     .
14425 8441   758.7 ""     .
14455 8441   762.3 ""     .
14487 8441   749.2 ""     .
14517 8441   748.6 ""     .
14546 8441   736.9 ""     .
14578 8441   727.8 ""     .
14609 8441     713 ""     .
14640 8441     689 ""     .
14669 8441   687.9 ""     .
14700 8441   686.5 ""     .
14728 8441     678 ""     .
14761 8441   660.6 ""     .
14791 8441   666.7 ""     .
14822 8441   648.7 ""     .
14853 8441   639.6 ""     .
14882 8441   639.4 ""     .
end

gen mdate = mofd(date)
format mdate %tm
isid id mdate, sort

capture frame drop merged
frame put _all if re == "M", into(merged)
drop if re == "M"

frame merged {
    collapse (sum) as, by(me mdate)
}

frlink 1:1 id mdate, frame(merged me mdate)
frget merged_as = as, from(merged)
replace as = as + merged_as if !missing(merged_as)

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Rafal Krol]

Thank you very much Clyde!
Definitely will use dataex next time!