I struggled to find a proper title for the following issue.
I have the following panel dataset
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str12 ISIN double(mkt_value Common_Equity_ Price_ Return_ Shrs_out_) float(date_ quarter_) byte d "" . . . . . 15340 167 1 "" . . . . . 15337 167 1 "ANN4327C1220" . 828 . . 40.083488 15340 167 1 "ANN4327C1220" 1215.5624310137 . 24.54298 25.541664 . 15337 167 1 "AT000000STR1" . . . . . 15337 167 1 "AT000000STR1" . . . . . 15340 167 1 "AT00000AMAG3" . . . . . 15340 167 1 "AT00000AMAG3" . . . . . 15337 167 1 "AT00000ATEC9" . . . . . 15337 167 1 "AT00000ATEC9" . . . . . 15340 167 1 "AT00000FACC2" . . . . . 15340 167 1 "AT00000FACC2" . . . . . 15337 167 1 "AT00000VIE62" 465.749988555908 . 7.5 .03334284 . 15337 167 1 "AT00000VIE62" . 532.4186 . . 84 15340 167 1 "AT0000609607" 137.863458336584 . 10.744474 -1.4492631 . 15337 167 1 "AT0000609607" . . . . . 15340 167 1 "AT0000640552" . 72 . . . 15340 167 1 "AT0000640552" 165 . 55 0 . 15337 167 1 "AT0000644505" 268.274994720097 . 10.428561 -4.6997366 . 15337 167 1 "AT0000644505" . 312.686 . . 25.725025725 15340 167 1 "AT0000690151" 3.54599995756149 . .9 28.571426 . 15337 167 1 "AT0000690151" . . . . . 15340 167 1 "AT0000698253" 3 . 1.7500019 -33.333332 . 15337 167 1 "AT0000698253" . . . . . 15340 167 1 "AT0000720008" 1629.25020572781 . 8.570373 37.925934 . 15337 167 1 "AT0000720008" . 2500.394 . . 543.150305 15340 167 1 "AT0000723606" 44.9100008690357 . 83.51907 -.20000339 . 15337 167 1 "AT0000723606" . 92.260461 . . .3 15340 167 1 "AT0000728209" . . . . . 15340 167 1 "AT0000728209" 43.8750011398352 . 23.591345 16.279066 . 15337 167 1 "AT0000730007" . 230.33 . . 104 15340 167 1 "AT0000730007" 276.899990081787 . 2.6625 1.4285684 . 15337 167 1 "AT0000741053" 1720.10323258498 . 11.305434 8.7690115 . 15337 167 1 "AT0000741053" . . . . . 15340 167 1 "AT0000743059" 2541.24008622796 . 9.358082 5.6934237 . 15337 167 1 "AT0000743059" . 2214.886 . . 270.59012965128 15340 167 1 "AT0000746409" . 1010 . . 307.8722 15340 167 1 "AT0000746409" 1268.55126402007 . 8.400001 -9.677416 . 15337 167 1 "AT0000758032" . . . . . 15340 167 1 "AT0000758032" 48.9356392323971 . 22 17.021286 . 15337 167 1 "AT0000758305" 214.523087820435 . 5.825 -9.759879 . 15337 167 1 "AT0000758305" . 128.262 . . 36.428 15340 167 1 "AT0000762406" . . . . . 15340 167 1 "AT0000762406" 10.9600000107288 . 1.6 -11.111111 . 15337 167 1 "AT0000767306" 10.7999997138977 . 7.2 -4.000002 . 15337 167 1 "AT0000767306" . 25.769 . . 1.5 15340 167 1 "AT0000785407" . . . . . 15340 167 1 "AT0000785407" 16.0026010748994 . 1.0891049 25.17482 . 15337 167 1 "AT0000785555" . 141.856 . . 20.573434 15340 167 1 "AT0000785555" 222.193079485594 . 9.5691595 10.429454 . 15337 167 1 "AT0000797303" . . . . . 15340 167 1 "AT0000797303" 11.2000000476837 . 20 53.846157 . 15337 167 1 "AT0000808209" . 17.584 . . .5948936117 15340 167 1 "AT0000808209" 28.4999988503396 . 45.907894 15.0399685 . 15337 167 1 "AT0000818802" 37.2707995173739 . 5.679731 20.789469 . 15337 167 1 "AT0000818802" . . . . . 15340 167 1 "AT0000831706" . 987.039 . . 72.505657552745 15340 167 1 "AT0000831706" 1093.9211887656 . 14.180188 -3.4926474 . 15337 167 1 "AT0000834007" 50.5999974155679 . 9.5440645 -27.19425 . 15337 167 1 "AT0000834007" . . . . . 15340 167 1 "AT0000837307" . . . . . 15337 167 1 "AT0000837307" . . . . . 15340 167 1 "AT0000922554" . 24.7509 . . 8.16 15340 167 1 "AT0000922554" 35.700001001358 . 5.25 12.299466 . 15337 167 1 "AT0000937503" . 1523.5 . . . 15340 167 1 "AT0000937503" 1059.299949646 . 8.025 6.9999933 . 15337 167 1 "AT0000938204" . 523.5376 . . 24 15340 167 1 "AT0000938204" 637.919998168945 . 26.58 -1.8826127 . 15337 167 1 "AT0000946652" . . . . . 15340 167 1 "AT0000946652" . . . . . 15337 167 1 "AT0000969985" . . . . . 15337 167 1 "AT0000969985" . 214.657 . . 29.78433164 15340 167 1 "AT0000A00XX9" . . . . . 15337 167 1 "AT0000A00XX9" . . . . . 15340 167 1 "AT0000A00Y78" . . . . . 15337 167 1 "AT0000A00Y78" . . . . . 15340 167 1 "AT0000A021K7" . . . . . 15340 167 1 "AT0000A021K7" . . . . . 15337 167 1 "AT0000A0E9W5" . 72.348978 . . 7.399600923 15340 167 1 "AT0000A0E9W5" 343.840020014772 . 45.222713 39.545475 . 15337 167 1 "AT0000A0Z9G3" . . . . . 15337 167 1 "AT0000A0Z9G3" . . . . . 15340 167 1 "AT0000A18XM4" . . . . . 15337 167 1 "AT0000A18XM4" . . . . . 15340 167 1 "AT0000A1PY49" . . . . . 15337 167 1 "AT0000A1PY49" . . . . . 15340 167 1 "AT0000APOST4" . . . . . 15340 167 1 "AT0000APOST4" . . . . . 15337 167 1 "AT0000KTMI02" . . . . . 15337 167 1 "AT0000KTMI02" . . . . . 15340 167 1 "AT000AGRANA3" 36.4499985839611 . 5.878755 29.255318 . 15337 167 1 "AT000AGRANA3" . . . . . 15340 167 1 "AT000KAPSCH9" . . . . . 15337 167 1 "AT000KAPSCH9" . . . . . 15340 167 1 "ATFREQUENT09" . . . . . 15337 167 1 "ATFREQUENT09" . . . . . 15340 167 1 "ATMARINOMED6" . . . . . 15337 167 1 "ATMARINOMED6" . . . . . 15340 167 1 "AU000000BKY0" . . . . . 15340 167 1 "AU000000BKY0" . . . . . 15337 167 1 end format %d date_ format %tq quarter_
Given that I have to run analysis on a quarter level, I've created the variable "quarter_" which extracts the quarter from "date_" variable.
I started to explore the data and I see some duplicates (identified by "d" variable) based on Isin and quarter_ .
This is because some variables are "registered" on, looking example above, 28dec2001 and others on 31dec2001, thus creating duplicates when we look on a quarter level.
Therefore my objective, for each duplicates, is to replace on the 31dec row the information contained in all the variables that actually are indicated on 28dec row. In this case I can have, for each ISIN, only one row (31dec), with all the information contained by all variables.
I acknowledge that I might have created confusion in the way that I explained the issue, but it's been puzzling to me this issue.
Thanks for your time
0 Response to Replacing values when duplicates
Post a Comment