I have data containing on retail investor trading (see below). The data set consists of trades at a discount broker form 1991 to 1996. In the first column you find the investor id, in the second column the date of transaction, the third column indicates the amount of stocks bought or sold (negative sign means sale of stock) and column 6 is an stock identifier. The other columns are not relevant for my problem.
Code:
* Example generated by -dataex-. For more info, type help dataex clear input long investor float(dates stocks price volume) str8 cusip long v2 str8 stringdates str1 v4 float v6 str4 v8 str3 v9 float v10 str9 v11 str11 v12 str9 v13 str6 v14 byte v15 2290 13366 100 54.375 5437.5 "06071610" 1263307 "19960805" "B" 55 "BIXN" "BA" 6 "COM" "54.37500" "060716107" "" . 2290 13408 200 25 5000 "17958410" 1518348 "19960916" "B" 80.1 "TTXX" "TB" 25 "COM" "25.00000" "179584107" "" . 2290 13382 200 21 4200 "86707110" 3433611 "19960821" "B" 75.35 "TTZZ" "TB" 25 "COM" "21.00000" "867071102" "" . 2290 13437 500 10 5000 "80850988" 9299931 "19961015" "B" 0 "DABD" "BA" 6 "MFS" "10.00000" "808509889" "" . 3206 11501 1000 2.25 2250 "02365010" 1101005 "19910628" "B" 90 "DDCP" "BA" 6 "COM" "2.25000" "023650104" "" . 3206 12404 -1000 1.0312 -1031.2 "02365010" 1101005 "19931217" "S" 90 "AZQD" "BA" 6 "COM" "1.03120" "023650104" "" . 3206 12977 -550 13.875 -7631.25 "96040210" 3814009 "19950713" "S" 91.75 "JWXX" "PC" 25 "COM" "13.87500" "960402105" "" . 3206 13457 257.732 19.4 5000 "08188080" 9212226 "19961104" "B" 0 "JOXX" "WW" 6 "MFF" "19.40000" "081880809" "" . 3206 11610 345.96 21.30001 7368.95 "47102340" 9240020 "19911015" "B" 15 "MFTM" "DE" 6 "MFC" "21.30001" "471023408" "" . 3206 13083 -369.231 29.87999 -11032.62 "47102340" 9240020 "19951027" "S" 66.33 "JWXX" "PC" 6 "MFA" "29.87999" "471023408" "" . 3206 11967 452.489 11.04999 5000 "77957Q10" 9268090 "19921006" "B" 30 "DDDB" "BA" 6 "MFC" "11.04999" "77957Q103" "" . 3206 11609 -122.52 34.82999 -4267.37 "81116510" 9272050 "19911014" "S" 29 "MFTM" "DE" 6 "MFC" "34.82999" "811165109" "" . 3206 11609 -194.999 16.27998 -3174.58 "81622110" 9273205 "19911014" "S" 29 "MFTM" "DE" 6 "MFC" "16.27998" "816221105" "" . 3206 11967 152.486 32.7899 5000 "92190830" 9289572 "19921006" "B" 30 "DDDB" "BA" 6 "MFC" "32.78990" "921908307" "" . 6002 13263 -12 43.875 -526.5 "03189710" 1008404 "19960424" "S" 35.1 "TTXX" "TB" 25 "COM" "43.87500" "031897101" "" . 6002 12177 100 16.5 1650 "02312710" 1098152 "19930504" "B" 55 "AZUD" "BA" 25 "COM" "16.50000" "023127103" "" . 6002 12257 -100 23.75 -2375 "02312710" 1098152 "19930723" "S" 55 "DDBK" "BA" 25 "COM" "23.75000" "023127103" "" . 6002 13439 50 19.75 987.5 "07251010" 1287879 "19961017" "B" 42.11 "TTXX" "TB" 2 "COM" "19.75000" "072510100" "" . 6002 13356 200 4.875 975 "41025210" 2134403 "19960726" "B" 41.92 "TTXX" "TB" 25 "COM" "4.87500" "410252100" "" . 6002 12075 1000 1.125 1125 "50221010" 2404508 "19930122" "B" 90 "CHRT" "BR" 25 "COM" "1.12500" "502210107" "" . 6002 12064 200 5.25 1050 "55261810" 2498081 "19930111" "B" 47.85 "CHJL" "BR" 25 "COM" "5.25000" "552618100" "" . 6002 12528 200 5.5 1100 "55261810" 2498081 "19940420" "B" 48.7 "DDTR" "BA" 25 "COM" "5.50000" "552618100" "" . 6002 13426 200 2.75 550 "55403410" 2501316 "19961004" "B" 35.42 "TTXX" "TB" 6 "COM" "2.75000" "554034108" "" . 6002 12263 100 11.625 1162.5 "55917720" 2511500 "19930729" "B" 49.76 "DDPR" "BA" 25 "COM" "11.62500" "559177209" "" . 6002 12498 -100 16.5 -1650 "55917720" 2511500 "19940321" "S" 55 "BIIU" "BA" 2 "COM" "16.50000" "559177209" "" . 6002 12435 100 6.875 687.5 "57679810" 2554418 "19940117" "B" 41.69 "DDTP" "BA" 6 "COM" "6.87500" "576798102" "" . 6002 12221 200 3.25 650 "59373610" 2602500 "19930617" "B" 41.05 "DDSH" "BA" 6 "COM" "3.25000" "593736101" "" . 6002 13276 -200 1.625 -325 "59373610" 2602500 "19960507" "S" 35.1 "TTXX" "TB" 6 "COM" "1.62500" "593736101" "" . 6002 13402 100 15.75 1575 "64121010" 2756430 "19960910" "B" 49.5 "TTCF" "TB" 6 "COM" "15.75000" "641210109" "" . 6002 11947 -100 10.75 -1075 "77310210" 3167145 "19920916" "S" 48.28 "CHNS" "BR" 25 "CEM" "10.75000" "773102108" "" . 6002 12058 100 18.625 1862.5 "81137170" 3256250 "19930105" "B" 55 "BIBC" "BA" 2 "COM" "18.62500" "811371707" "" . 6002 12088 -100 23.75 -2375 "81137170" 3256250 "19930204" "S" 55 "DDCC" "BA" 2 "COM" "23.75000" "811371707" "" . 6002 12092 100 8.625 862.5 "81090510" 3256880 "19930208" "B" 44.66 "DDAD" "BA" 25 "CEM" "8.62500" "810905109" "" . 6002 13137 -100 11.25 -1125 "81090510" 3256880 "19951220" "S" 44.21 "TTXX" "TB" 25 "CEM" "11.25000" "810905109" "" . 6002 13408 200 4.5625 912.5 "87990540" 3495032 "19960916" "B" 40.96 "TTXX" "TB" 6 "COM" "4.56250" "879905404" "" . 6002 13460 100 1.75 175 "87990540" 3495032 "19961107" "B" 35.1 "TTXX" "TB" 6 "COM" "1.75000" "879905404" "" . 6002 13368 -6746.722 1 -6746.72 "31606710" 9230541 "19960807" "S" 0 "AYDY" "BA" 6 "MPM" "1.00000" "316067107" "" . 6002 11948 -60.136 65.36999 -3931.09 "31618410" 9230555 "19920917" "S" 29 "MFSC" "DE" 6 "PFA" "65.36999" "316184100" "" . 6002 13356 53.433 36.70017 1961 "62838030" 9257430 "19960726" "B" 39 "BJDU" "BA" 6 "MFC" "36.70017" "628380305" "" . 6002 12667 226.655 11.02998 2500 "77064830" 9268407 "19940906" "B" 39 "OZBI" "BA" 6 "MFC" "11.02998" "770648301" "" . 6002 13156 116.279 10.32001 1200 "77064887" 9268408 "19960108" "B" 0 "OZTH" "BA" 6 "MFA" "10.32001" "770648871" "" . 6002 13346 62.814 11.94001 750 "77064887" 9268408 "19960716" "B" 0 "TTXX" "TB" 6 "MFA" "11.94001" "770648871" "" . 7109 12445 100 32 3200 "00157530" 1007205 "19940127" "B" 49.5 "TTXX" "TB" 3 "COM" "32.00000" "001575307" "" . 7109 12514 -100 34.375 -3437.5 "00157530" 1007205 "19940406" "S" 49.5 "TTXX" "TB" 25 "COM" "34.37500" "001575307" "" . 7109 11396 100 22.25 2225 "00190710" 1011850 "19910315" "B" 49 "IROM" "BR" 6 "COM" "22.25000" "001907104" "" . 7109 12403 -100 23.5 -2350 "00190710" 1011850 "19931216" "S" 49.5 "TTVT" "TB" 6 "COM" "23.50000" "001907104" "" . 7109 11553 100 21.75 2175 "01849010" 1076690 "19910819" "B" 49.5 "TTXX" "TB" 3 "COM" "21.75000" "018490102" "" . 7109 12087 -100 22.5 -2250 "01849010" 1076690 "19930203" "S" 49.5 "TTCF" "TB" 3 "COM" "22.50000" "018490102" "" . 7109 11354 -100 24.875 -2487.5 "03673210" 1177583 "19910201" "S" 44.1 "TTSY" "TB" 2 "COM" "24.87500" "036732105" "" . 7109 12380 100 20.75 2075 "03785710" 1180941 "19931123" "B" 49.5 "TTXX" "TB" 6 "COM" "20.75000" "037857109" "" . 7109 12516 -100 25.75 -2575 "03785710" 1180941 "19940408" "S" 49.5 "TTXX" "TB" 6 "COM" "25.75000" "037857109" "" . 7109 13269 100 26.25 2625 "03785710" 1180941 "19960430" "B" 49.5 "TTXX" "TB" 6 "COM" "26.25000" "037857109" "" . 7109 12697 150 16.5 2475 "04890310" 1224517 "19941006" "B" 64.87 "TTXX" "TB" 25 "COM" "16.50000" "048903108" "" . 7109 12228 100 20.875 2087.5 "09367610" 1324410 "19930624" "B" 49.5 "TTXX" "TB" 2 "COM" "20.87500" "093676104" "" . 7109 12472 -100 24.75 -2475 "09367610" 1324410 "19940223" "S" 49.5 "TTQF" "TB" 25 "COM" "24.75000" "093676104" "" . 7109 12134 50 65.5 3275 "10904310" 1356402 "19930322" "B" 49.5 "TTXX" "TB" 3 "COM" "65.50000" "109043109" "" . 7109 12589 -50 72.5 -3625 "10904310" 1356402 "19940620" "S" 49.5 "TTXX" "TB" 3 "COM" "72.50000" "109043109" "" . 7109 12697 50 68.375 3418.75 "10904310" 1356402 "19941006" "B" 49.5 "TTXX" "TB" 3 "COM" "68.37500" "109043109" "" . 7109 13443 50 62.875 3143.75 "12692010" 1397275 "19961021" "B" 49.5 "TTXX" "TB" 3 "COM" "62.87500" "126920107" "" . 7109 11340 100 27.25 2725 "20810810" 1596215 "19910118" "B" 44.1 "TTFK" "TB" 25 "COM" "27.25000" "208108100" "" . 7109 11353 -100 27.25 -2725 "20810810" 1596215 "19910131" "S" 44.1 "TTPS" "TB" 25 "COM" "27.25000" "208108100" "" . 7109 11416 100 24 2400 "20810810" 1596215 "19910404" "B" 49 "IROM" "BR" 3 "COM" "24.00000" "208108100" "" . 7109 12087 -100 23.375 -2337.5 "20810810" 1596215 "19930203" "S" 49.5 "TTCF" "TB" 25 "COM" "23.37500" "208108100" "" . 7109 12362 100 26.125 2612.5 "22237210" 1647318 "19931105" "B" 49.5 "TTXX" "TB" 3 "COM" "26.12500" "222372104" "" . 7109 12514 -100 23.625 -2362.5 "22237210" 1647318 "19940406" "S" 49.5 "TTXX" "TB" 25 "COM" "23.62500" "222372104" "" . 7109 13355 200 13.75 2750 "23281510" 1676215 "19960725" "B" 66.74 "TTBM" "TB" 6 "COM" "13.75000" "232815100" "" . 7109 11926 100 33 3300 "25084710" 1724800 "19920826" "B" 49.5 "TTXX" "TB" 25 "COM" "33.00000" "250847100" "" . 7109 12362 50 30.875 1543.75 "25084710" 1724800 "19931105" "B" 49.5 "TTXX" "TB" 6 "COM" "30.87500" "250847100" "" . 7109 12607 -150 25.125 -3768.75 "25084710" 1724800 "19940708" "S" 72.79 "TTXX" "TB" 2 "COM" "25.12500" "250847100" "" . 7109 11548 100 27.5 2750 "25283630" 1734460 "19910814" "B" 49.5 "TT08" "TB" 25 "COM" "27.50000" "252836309" "" . 7109 12137 -100 12.125 -1212.5 "25283630" 1734460 "19930325" "S" 45.55 "TTJD" "TB" 25 "COM" "12.12500" "252836309" "" . 7109 12095 50 48.375 2418.75 "38131710" 2071401 "19930211" "B" 49.5 "TTXX" "TB" 6 "COM" "48.37500" "381317106" "" . 7109 12163 -50 44.125 -2206.25 "38131710" 2071401 "19930420" "S" 49.5 "TTXM" "TB" 6 "COM" "44.12500" "381317106" "" . 7109 12715 200 13.125 2625 "38526910" 2083780 "19941024" "B" 65.99 "TTXX" "TB" 25 "COM" "13.12500" "385269105" "" . 7109 13464 -300 12.625 -3787.5 "38526910" 2083780 "19961111" "S" 72.9 "TTXX" "TB" 25 "COM" "12.62500" "385269105" "" . 7109 13031 100 44 4400 "40621610" 2127601 "19950905" "B" 49.5 "TTXX" "TB" 25 "COM" "44.00000" "406216101" "" . 7109 12430 100 28.75 2875 "44984210" 2232221 "19940112" "B" 49.5 "TTXX" "TB" 25 "MLP" "28.75000" "449842103" "" . 7109 13464 -100 13 -1300 "44984210" 2232221 "19961111" "S" 46.89 "TTXX" "TB" 3 "MLP" "13.00000" "449842103" "" . 7109 12290 100 40.875 4087.5 "50025510" 2394425 "19930825" "B" 49.5 "TTXX" "TB" 3 "COM" "40.87500" "500255104" "" . 7109 12409 -100 46.625 -4662.5 "50025510" 2394425 "19931222" "S" 49.5 "TTQR" "TB" 3 "COM" "46.62500" "500255104" "" . 7109 12179 100 28.5 2850 "53567810" 2468512 "19930506" "B" 49.5 "TTXX" "TB" 6 "COM" "28.50000" "535678106" "" . 7109 12575 -100 46.5 -4650 "53567810" 2468512 "19940606" "S" 49.5 "TTXX" "TB" 6 "COM" "46.50000" "535678106" "" . 7109 12738 50 51 2550 "53567810" 2468512 "19941116" "B" 49.5 "TTXX" "TB" 6 "COM" "51.00000" "535678106" "" . 7109 12817 -50 51.5 -2575 "53567810" 2468512 "19950203" "S" 49.5 "TTXX" "TB" 6 "COM" "51.50000" "535678106" "" . 7109 13234 100 40.375 4037.5 "53567810" 2468512 "19960326" "B" 49.5 "TTXX" "TB" 6 "COM" "40.37500" "535678106" "" . 7109 11728 100 19.625 1962.5 "59408710" 2603659 "19920210" "B" 49.5 "TTXX" "TB" 6 "COM" "19.62500" "594087108" "" . 7109 12319 50 36.625 1831.25 "59408710" 2603659 "19930923" "B" 49.5 "TTXX" "TB" 6 "COM" "36.62500" "594087108" "" . 7109 12395 -150 32.25 -4837.5 "59408710" 2603659 "19931208" "S" 74.25 "TTVT" "TB" 6 "COM" "32.25000" "594087108" "" . 7109 12282 100 21.5 2150 "72387710" 2983605 "19930817" "B" 49.5 "TTXX" "TB" 6 "COM" "21.50000" "723877106" "" . 7109 12418 -100 18.75 -1875 "72387710" 2983605 "19931231" "S" 49.5 "TTQH" "TB" 6 "COM" "18.75000" "723877106" "" . 7109 11354 -100 9.25 -925 "74838C10" 3071757 "19910201" "S" 36.72 "TTFK" "TB" 6 "COM" "9.25000" "74838C106" "" . 7109 12249 50 30.75 1537.5 "76340810" 3144386 "19930715" "B" 55 "DDNC" "BA" 6 "COM" "30.75000" "763408101" "" . 7109 12624 -200 13.75 -2750 "76340810" 3144386 "19940725" "S" 66.74 "TTXX" "TB" 6 "COM" "13.75000" "763408101" "" . 7109 12627 -300 15 -4500 "76340810" 3144386 "19940728" "S" 77.13 "TTXX" "TB" 6 "COM" "15.00000" "763408101" "" . 7109 12697 100 20.25 2025 "77136710" 3166007 "19941006" "B" 49.5 "TTXX" "TB" 25 "COM" "20.25000" "771367109" "" . 7109 12472 100 27.25 2725 "80818810" 3241320 "19940223" "B" 49.5 "TTXX" "TB" 6 "COM" "27.25000" "808188106" "" . 7109 12514 -100 27.75 -2775 "80818810" 3241320 "19940406" "S" 49.5 "TTXX" "TB" 6 "COM" "27.75000" "808188106" "" . 7109 11396 100 36.875 3687.5 "86331410" 3421805 "19910315" "B" 49 "IROM" "BR" 2 "COM" "36.87500" "863314100" "" . 7109 12075 -200 19.875 -3975 "86331410" 3421805 "19930122" "S" 74.01 "TTTM" "TB" 2 "COM" "19.87500" "863314100" "" . 7109 12479 100 21 2100 "86693310" 3432082 "19940302" "B" 49.5 "TTXX" "TB" 2 "COM" "21.00000" "866933104" "" . end format %td dates
For example, in line 7: investor 3206 sold an amount of 550 stocks with an stock identifier of 96040210, because there is not a corresponding purchase of the stock, one can assume that the stock was purchased before 1991 and therefore I want to remove this observation.
The same holds for line 9 and 10, there the accumulated share position is negative and therefore I want to drop line 9 and 10.
I know that I can collapse the data set:
Code:
collapse (sum) stocks, by(investor cusip)
Maybe someone has an idea how to cope with this problem. Thanks in advance!
0 Response to Removing unnecessary observations
Post a Comment