Hi
-My main concern are the 1262 and 1272 observations in the distcd.
-First, for the same company (identified by the individual permno) I am looking to keep the observations which are one month before the 1262 or 1272. (along with keeping these observations of 1262 and 1272).
Hypothetical example: keep monthdate 201111 for permno 93417 if distcd 1262 exists in 201112 for permno 93417. (please note dates are in numeric form).
-Next, I would like to create 3 different databases....
Database 1: Contains 1262 or 1272 observations, where each firms DIVAMT is 10% or greater of the previous month's PRC.
Database 2: Contains 1262 or 1272 observations, where each firms DIVAMT is >5% and <10% of the previous month's PRC.
Database 3: Contains all the remaining 1262 and 1272 observations.
Please note: I need the one month prior observations merely for calculation purposes. See sample data below.
DATE | PERMNO | DISTCD | DIVAMT | PRC | MONTHDATE |
19330531 | 10014 | . | . | 18 | 193305 |
19330630 | 10014 | . | . | 21.625 | 193306 |
19330731 | 10014 | 1232 | 0.15 | 22.875 | 193307 |
19330831 | 10014 | . | . | 30.25 | 193308 |
19330930 | 10014 | . | . | 26.25 | 193309 |
19331031 | 10014 | 1232 | 0.15 | 25.625 | 193310 |
19331031 | 10014 | 1272 | 0.15 | 25.625 | 193310 |
19331129 | 10014 | . | . | 22.625 | 193311 |
19331230 | 10014 | . | . | 22.375 | 193312 |
19340131 | 10014 | 1232 | 0.15 | 21.75 | 193401 |
19340131 | 10014 | 1272 | 0.15 | 21.75 | 193401 |
19340228 | 10014 | . | . | 19.75 | 193402 |
19340331 | 10014 | . | . | 22.625 | 193403 |
19340430 | 10014 | 1232 | 0.15 | 19.625 | 193404 |
19340430 | 10014 | 1272 | 0.15 | 19.625 | 193404 |
19340531 | 10014 | . | . | 19.5 | 193405 |
19340630 | 10014 | . | . | 21.125 | 193406 |
19340731 | 10014 | 1232 | 0.15 | 19.375 | 193407 |
19340731 | 10014 | 1272 | 0.15 | 19.375 | 193407 |
19340831 | 10014 | . | . | 19 | 193408 |
19340929 | 10014 | 1232 | 0.15 | 20.25 | 193409 |
19341031 | 10014 | 1272 | 0.15 | 17.875 | 193410 |
19341130 | 10014 | . | . | 17.625 | 193411 |
19341231 | 10014 | . | . | 18.875 | 193412 |
19350131 | 10014 | 1232 | 0.15 | 17.375 | 193501 |
19350131 | 10014 | 1272 | 0.15 | 17.375 | 193501 |
19350228 | 10014 | . | . | 16.5 | 193502 |
19350330 | 10014 | . | . | 16.25 | 193503 |
19350430 | 10014 | 1232 | 0.15 | 17.75 | 193504 |
19350430 | 10014 | 1272 | 0.15 | 17.75 | 193504 |
19350531 | 10014 | . | . | 17.25 | 193505 |
19350629 | 10014 | . | . | 16.75 | 193506 |
19350731 | 10014 | 1232 | 0.15 | 15.5 | 193507 |
19350731 | 10014 | 1272 | 0.15 | 15.5 | 193507 |
19350831 | 10014 | . | . | 16.5 | 193508 |
19350930 | 10014 | . | . | 15.5 | 193509 |
19351031 | 10014 | 1232 | 0.15 | 13.875 | 193510 |
19351031 | 10014 | 1272 | 0.15 | 13.875 | 193510 |
19351130 | 10014 | . | . | 15.375 | 193511 |
19351231 | 10014 | . | . | 15.375 | 193512 |
19360131 | 10014 | 1232 | 0.15 | 16.375 | 193601 |
19360131 | 10014 | 1272 | 0.15 | 16.375 | 193601 |
19360229 | 10014 | . | . | 15.5 | 193602 |
19360331 | 10014 | . | . | 15 | 193603 |
19360430 | 10014 | 1232 | 0.15 | 14.375 | 193604 |
19360430 | 10014 | 1272 | 0.15 | 14.375 | 193604 |
19360529 | 10014 | . | . | 14.25 | 193605 |
19360630 | 10014 | . | . | 14.125 | 193606 |
19360731 | 10014 | 1232 | 0.15 | 13.625 | 193607 |
19360731 | 10014 | 1272 | 0.15 | 13.625 | 193607 |
19360831 | 10014 | . | . | 14.625 | 193608 |
19360930 | 10014 | . | . | 16.75 | 193609 |
19361031 | 10014 | 1232 | 0.15 | 15.25 | 193610 |
19361031 | 10014 | 1272 | 0.15 | 15.25 | 193610 |
19361130 | 10014 | . | . | 14.5 | 193611 |
19361231 | 10014 | . | . | 14.625 | 193612 |
19370130 | 10014 | 1212 | 0.15 | 14.5 | 193701 |
19370130 | 10014 | 1272 | 0.15 | 14.5 | 193701 |
19370227 | 10014 | . | . | 15 | 193702 |
19370331 | 10014 | . | . | 14.625 | 193703 |
19370430 | 10014 | 1212 | 0.15 | 12.5 | 193704 |
19370430 | 10014 | 1272 | 0.15 | 12.5 | 193704 |
19370528 | 10014 | . | . | 12 | 193705 |
19370630 | 10014 | . | . | 11.375 | 193706 |
19370731 | 10014 | 1212 | 0.15 | -11.6875 | 193707 |
19370731 | 10014 | 1272 | 0.15 | -11.6875 | 193707 |
19370831 | 10014 | . | . | 11.625 | 193708 |
19370930 | 10014 | . | . | 12.25 | 193709 |
19371030 | 10014 | 1212 | 0.15 | 11.375 | 193710 |
19371030 | 10014 | 1272 | 0.3 | 11.375 | 193710 |
19371130 | 10014 | . | . | 11.625 | 193711 |
19371231 | 10014 | . | . | 11.5 | 193712 |
19380131 | 10014 | 1212 | 0.15 | 12.5 | 193801 |
19380131 | 10014 | 1272 | 0.15 | 12.5 | 193801 |
19380228 | 10014 | . | . | 11.75 | 193802 |
19380331 | 10014 | . | . | 8.75 | 193803 |
19380430 | 10014 | 1212 | 0.15 | 9.625 | 193804 |
19380430 | 10014 | 1272 | 0.15 | 9.625 | 193804 |
19380531 | 10014 | . | . | 9.875 | 193805 |
19380630 | 10014 | . | . | 10.5 | 193806 |
19380730 | 10014 | 1212 | 0.15 | 11 | 193807 |
19380730 | 10014 | 1272 | 0.15 | 11 | 193807 |
19380831 | 10014 | . | . | 10.375 | 193808 |
19380930 | 10014 | . | . | 9.875 | 193809 |
19381031 | 10014 | 1212 | 0.15 | 9.5 | 193810 |
19381031 | 10014 | 1272 | 0.15 | 9.5 | 193810 |
19381130 | 10014 | . | . | 9.875 | 193811 |
19381231 | 10014 | 1232 | 0.25 | 9.875 | 193812 |
19390131 | 10014 | . | . | 9.75 | 193901 |
19390228 | 10014 | . | . | 9.75 | 193902 |
19390331 | 10014 | . | . | 8.875 | 193903 |
19390429 | 10014 | 1232 | 0.25 | 8 | 193904 |
19390531 | 10014 | . | . | 8.5 | 193905 |
19390630 | 10014 | 1232 | 0.15 | 7.125 | 193906 |
19390731 | 10014 | . | . | 7.5 | 193907 |
19390831 | 10014 | . | . | 6.875 | 193908 |
19390930 | 10014 | . | . | 6.75 | 193909 |
19391031 | 10014 | 1232 | 0.15 | 6.875 | 193910 |
19391130 | 10014 | . | . | 6.75 | 193911 |
19391230 | 10014 | . | . | 6.375 | 193912 |
19400131 | 10014 | 1212 | 0.15 | 6.625 | 194001 |
19400229 | 10014 | . | . | 6.25 | 194002 |
19400330 | 10014 | . | . | 6.25 | 194003 |
19400430 | 10014 | 1212 | 0.15 | 6.125 | 194004 |
19400531 | 10014 | . | . | 4.625 | 194005 |
19400629 | 10014 | . | . | 4.5 | 194006 |
19400731 | 10014 | 1212 | 0.15 | 4.5 | 194007 |
19400831 | 10014 | . | . | 4.5 | 194008 |
19400930 | 10014 | . | . | 4.75 | 194009 |
19401031 | 10014 | 1212 | 0.15 | 4.75 | 194010 |
19401130 | 10014 | . | . | 5.25 | 194011 |
19401231 | 10014 | . | . | 4.625 | 194012 |
19410131 | 10014 | 1212 | 0.125 | 4.625 | 194101 |
19410228 | 10014 | . | . | 4.5 | 194102 |
19410331 | 10014 | . | . | 4.375 | 194103 |
19410430 | 10014 | 1212 | 0.125 | 4 | 194104 |
19410531 | 10014 | . | . | 4 | 194105 |
19410630 | 10014 | . | . | 4 | 194106 |
19410731 | 10014 | 1212 | 0.125 | 4.375 | 194107 |
19410830 | 10014 | . | . | 4.25 | 194108 |
19410930 | 10014 | . | . | 3.25 | 194109 |
19411031 | 10014 | . | . | 2.5 | 194110 |
19411129 | 10014 | . | . | 2.625 | 194111 |
19411231 | 10014 | . | . | 1.625 | 194112 |
19420131 | 10014 | . | . | 2.375 | 194201 |
19420228 | 10014 | . | . | 2 | 194202 |
19420331 | 10014 | . | . | 1.75 | 194203 |
19870227 | 10016 | 1232 | 0.22 | 71.5 | 198702 |
19870331 | 10016 | . | . | 72 | 198703 |
19870430 | 10016 | . | . | 68 | 198704 |
19870529 | 10016 | 1232 | 0.22 | 67.5 | 198705 |
19870630 | 10016 | . | . | 72.5 | 198706 |
19870731 | 10016 | . | . | 78.5 | 198707 |
19870831 | 10016 | 1232 | 0.22 | 79.75 | 198708 |
19870930 | 10016 | . | . | 74 | 198709 |
19871030 | 10016 | . | . | 45.5 | 198710 |
19871130 | 10016 | 1232 | 0.23 | 49.75 | 198711 |
19871231 | 10016 | . | . | 53.5 | 198712 |
19880129 | 10016 | . | . | 56 | 198801 |
19880229 | 10016 | 1232 | 0.23 | 58.5 | 198802 |
19880331 | 10016 | . | . | 63.5 | 198803 |
19880429 | 10016 | . | . | 70 | 198804 |
19880531 | 10016 | 1232 | 0.23 | 71 | 198805 |
19880630 | 10016 | . | . | 68 | 198806 |
19880729 | 10016 | . | . | 67 | 198807 |
19880831 | 10016 | 1232 | 0.23 | 66.25 | 198808 |
19880930 | 10016 | . | . | 67.25 | 198809 |
19881031 | 10016 | . | . | 79 | 198810 |
19881130 | 10016 | 1232 | 0.25 | 73 | 198811 |
19881230 | 10016 | 1272 | 0.2 | 74 | 198812 |
19890131 | 10016 | . | . | 78 | 198901 |
19890228 | 10016 | 1232 | 0.3 | 78 | 198902 |
19890331 | 10016 | . | . | 78 | 198903 |
19890428 | 10016 | . | . | 74.5 | 198904 |
19890531 | 10016 | 1232 | 0.3 | 74.5 | 198905 |
19890630 | 10016 | . | . | 77 | 198906 |
19890731 | 10016 | . | . | 69 | 198907 |
19890831 | 10016 | 1232 | 0.3 | 77 | 198908 |
19890929 | 10016 | . | . | 78.75 | 198909 |
19891031 | 10016 | . | . | 76 | 198910 |
19891130 | 10016 | 1232 | 0.3 | -73.5 | 198911 |
19891229 | 10016 | . | . | 65 | 198912 |
19900131 | 10016 | 5523 | 0 | 23 | 199001 |
19900228 | 10016 | 1232 | 0.125 | 24 | 199002 |
19900330 | 10016 | . | . | 23.75 | 199003 |
19900430 | 10016 | . | . | 22.75 | 199004 |
19900531 | 10016 | 1232 | 0.125 | 24.25 | 199005 |
19900629 | 10016 | . | . | 24.75 | 199006 |
19900731 | 10016 | . | . | 23 | 199007 |
19900831 | 10016 | 1232 | 0.125 | 17.5 | 199008 |
19900928 | 10016 | . | . | 15.5 | 199009 |
19901031 | 10016 | . | . | 12 | 199010 |
19901130 | 10016 | 1232 | 0.125 | 12 | 199011 |
19901231 | 10016 | . | . | 13.75 | 199012 |
19910131 | 10016 | . | . | 13.25 | 199101 |
19270831 | 10022 | . | . | 47.375 | 192708 |
19270930 | 10022 | 5533 | 0 | 51.5 | 192709 |
19270930 | 10022 | 1232 | 0.75 | 51.5 | 192709 |
19271031 | 10022 | . | . | 56 | 192710 |
19271130 | 10022 | . | . | 58 | 192711 |
19271231 | 10022 | 1232 | 1 | 58.5 | 192712 |
19271231 | 10022 | 1272 | 0.25 | 58.5 | 192712 |
19280131 | 10022 | . | . | -57.1875 | 192801 |
19280229 | 10022 | . | . | 58 | 192802 |
19280331 | 10022 | 1232 | 1 | 62.5 | 192803 |
19280331 | 10022 | 1272 | 0.25 | 62.5 | 192803 |
19280430 | 10022 | . | . | 64 | 192804 |
19280531 | 10022 | . | . | 67.625 | 192805 |
19280630 | 10022 | 1232 | 1 | 64 | 192806 |
19280630 | 10022 | 1272 | 0.25 | 64 | 192806 |
19280731 | 10022 | . | . | 63 | 192807 |
19280831 | 10022 | . | . | 69.125 | 192808 |
19280929 | 10022 | 1232 | 1 | 71 | 192809 |
19280929 | 10022 | 1272 | 0.25 | 71 | 192809 |
19281031 | 10022 | . | . | 73.5 | 192810 |
19281130 | 10022 | . | . | 72.5 | 192811 |
19281231 | 10022 | 1232 | 1 | 68 | 192812 |
19281231 | 10022 | 1272 | 0.25 | 68 | 192812 |
19290131 | 10022 | . | . | 73.75 | 192901 |
19290228 | 10022 | . | . | 69 | 192902 |
19290328 | 10022 | 1232 | 1 | 65 | 192903 |
19290328 | 10022 | 1272 | 0.25 | 65 | 192903 |
19290430 | 10022 | . | . | 67 | 192904 |
19290531 | 10022 | . | . | 64.75 | 192905 |
19290629 | 10022 | 1232 | 1 | . | 192906 |
19290629 | 10022 | 1272 | 0.25 | . | 192906 |
19290731 | 10022 | . | . | 65.5 | 192907 |
19290830 | 10022 | . | . | 69 | 192908 |
19290930 | 10022 | 1232 | 1 | -64.75 | 192909 |
19290930 | 10022 | 1272 | 0.25 | -64.75 | 192909 |
19291031 | 10022 | . | . | 57 | 192910 |
19291127 | 10022 | . | . | 57.375 | 192911 |
19291231 | 10022 | 1232 | 1.25 | 59 | 192912 |
19300131 | 10022 | . | . | 62.375 | 193001 |
19300228 | 10022 | . | . | 63.375 | 193002 |
19300331 | 10022 | 1232 | 1.25 | 62.5 | 193003 |
19300430 | 10022 | . | . | 66.875 | 193004 |
19300529 | 10022 | . | . | -65.3125 | 193005 |
19300630 | 10022 | 1232 | 1.25 | 58.875 | 193006 |
19300731 | 10022 | . | . | 62.375 | 193007 |
19300829 | 10022 | . | . | 63.75 | 193008 |
19300930 | 10022 | 1232 | 1.25 | 61 | 193009 |
19301031 | 10022 | . | . | 59.25 | 193010 |
19301129 | 10022 | . | . | -58.75 | 193011 |
19301231 | 10022 | 1232 | 1.25 | 58.5 | 193012 |
19310131 | 10022 | . | . | 62.75 | 193101 |
19310228 | 10022 | . | . | 64.375 | 193102 |
19310331 | 10022 | 1232 | 1.25 | -57.0625 | 193103 |
19310430 | 10022 | . | . | 49.75 | 193104 |
19310529 | 10022 | . | . | 49 | 193105 |
19310630 | 10022 | 1232 | 1.25 | 49 | 193106 |
19310731 | 10022 | . | . | 49.75 | 193107 |
19310831 | 10022 | . | . | 48.375 | 193108 |
19310930 | 10022 | 1232 | 1.25 | 30 | 193109 |
19311031 | 10022 | . | . | 34.5 | 193110 |
19311130 | 10022 | . | . | 31 | 193111 |
19311231 | 10022 | 1232 | 1.25 | 23.5 | 193112 |
19320130 | 10022 | . | . | -22.375 | 193201 |
19320229 | 10022 | . | . | 25.5 | 193202 |
19320331 | 10022 | 1232 | 0.75 | 22.25 | 193203 |
19320430 | 10022 | . | . | -18.0625 | 193204 |
19320531 | 10022 | . | . | 14.5 | 193205 |
19320630 | 10022 | 1232 | 0.75 | 15 | 193206 |
19320730 | 10022 | . | . | -20.6875 | 193207 |
19320831 | 10022 | . | . | 23.25 | 193208 |
19320930 | 10022 | 1232 | 0.75 | -22.625 | 193209 |
19321031 | 10022 | . | . | -20 | 193210 |
19321130 | 10022 | . | . | . | 193211 |
19321231 | 10022 | 1232 | 0.75 | 22.875 | 193212 |
19330131 | 10022 | . | . | 21.5 | 193301 |
19340731 | 10022 | . | . | 52.5 | 193407 |
19340831 | 10022 | . | . | -54.75 | 193408 |
19340929 | 10022 | 1232 | 1 | -54.25 | 193409 |
19341031 | 10022 | . | . | -54.625 | 193410 |
19341130 | 10022 | . | . | 63.25 | 193411 |
19341231 | 10022 | 1232 | 1 | 68 | 193412 |
19350131 | 10022 | . | . | -69.6875 | 193501 |
19350228 | 10022 | . | . | 74.875 | 193502 |
19350330 | 10022 | 1232 | 1 | -67.375 | 193503 |
19350330 | 10022 | 1272 | 0.25 | -67.375 | 193503 |
19350430 | 10022 | . | . | 74 | 193504 |
19350531 | 10022 | . | . | 76.625 | 193505 |
19350629 | 10022 | 1232 | 1.25 | -82.75 | 193506 |
19350731 | 10022 | . | . | 93 | 193507 |
19350831 | 10022 | . | . | -89.5 | 193508 |
19350930 | 10022 | 1232 | 1.25 | 94 | 193509 |
19351031 | 10022 | . | . | -91 | 193510 |
19351130 | 10022 | . | . | 94 | 193511 |
19351231 | 10022 | 1232 | 1.25 | 91 | 193512 |
19360131 | 10022 | . | . | -94 | 193601 |
19360229 | 10022 | . | . | -111.625 | 193602 |
19360331 | 10022 | 1232 | 1.5 | -109.75 | 193603 |
19360430 | 10022 | . | . | 99 | 193604 |
19360529 | 10022 | . | . | -101.75 | 193605 |
19360630 | 10022 | 1232 | 1.5 | -108.625 | 193606 |
19360731 | 10022 | . | . | -107.6875 | 193607 |
19360831 | 10022 | 5523 | 0 | 38.5 | 193608 |
19360930 | 10022 | 1232 | 0.5 | -37.25 | 193609 |
19361031 | 10022 | . | . | 37.75 | 193610 |
19361130 | 10022 | 1232 | 0.5 | 36 | 193611 |
19361130 | 10022 | 1272 | 0.25 | 36 | 193611 |
19361231 | 10022 | . | . | 33.25 | 193612 |
19370130 | 10022 | . | . | 34.75 | 193701 |
19370227 | 10022 | . | . | 34 | 193702 |
19370331 | 10022 | 1212 | 0.5 | 31.875 | 193703 |
19370430 | 10022 | . | . | 29.5 | 193704 |
19370528 | 10022 | . | . | -29.625 | 193705 |
19370630 | 10022 | 1212 | 0.5 | 27.75 | 193706 |
19370731 | 10022 | . | . | -29.25 | 193707 |
19370831 | 10022 | . | . | 27 | 193708 |
19370930 | 10022 | 1212 | 0.5 | 24.25 | 193709 |
19371030 | 10022 | . | . | 23 | 193710 |
19371130 | 10022 | . | . | 18.5 | 193711 |
19371231 | 10022 | 1212 | 0.5 | 15.75 | 193712 |
19380131 | 10022 | . | . | -19.5625 | 193801 |
19380228 | 10022 | . | . | 19.5 | 193802 |
19380331 | 10022 | 1212 | 0.5 | 15.375 | 193803 |
19380430 | 10022 | . | . | -17.25 | 193804 |
19380531 | 10022 | . | . | 16.75 | 193805 |
19380630 | 10022 | 1212 | 0.4 | 17 | 193806 |
19380730 | 10022 | . | . | 18.5 | 193807 |
19380831 | 10022 | . | . | -17.875 | 193808 |
19380930 | 10022 | 1212 | 0.4 | 17.25 | 193809 |
19381031 | 10022 | . | . | 16.875 | 193810 |
19381130 | 10022 | . | . | 16.25 | 193811 |
19381231 | 10022 | 1212 | 0.4 | 14.875 | 193812 |
19420331 | 10022 | . | . | 5.125 | 194203 |
19420430 | 10022 | 1212 | 0.25 | 5.25 | 194204 |
19420529 | 10022 | . | . | 5.875 | 194205 |
19420630 | 10022 | . | . | -5.8125 | 194206 |
19420731 | 10022 | . | . | 6.375 | 194207 |
19420831 | 10022 | . | . | -7.1875 | 194208 |
19420930 | 10022 | . | . | 7.5 | 194209 |
19421031 | 10022 | 1212 | 0.25 | -7.75 | 194210 |
19421130 | 10022 | . | . | 9.25 | 194211 |
19421231 | 10022 | 1272 | 0.25 | -9.1875 | 194212 |
19430130 | 10022 | . | . | 9.625 | 194301 |
19430227 | 10022 | . | . | 13 | 194302 |
19430331 | 10022 | . | . | 14 | 194303 |
19430430 | 10022 | 1212 | 0.5 | 13.875 | 194304 |
19430529 | 10022 | . | . | 14.25 | 194305 |
19430630 | 10022 | . | . | 14.25 | 194306 |
19430731 | 10022 | . | . | 13.75 | 194307 |
19430831 | 10022 | . | . | -13.625 | 194308 |
19430930 | 10022 | . | . | 14 | 194309 |
19431030 | 10022 | 1212 | 0.5 | -14.25 | 194310 |
19431130 | 10022 | . | . | -13.875 | 194311 |
19431231 | 10022 | . | . | 13.75 | 194312 |
19440131 | 10022 | . | . | 14.5 | 194401 |
19440229 | 10022 | . | . | -14.625 | 194402 |
19440331 | 10022 | . | . | 16 | 194403 |
19440429 | 10022 | 1212 | 0.5 | -15.375 | 194404 |
19440531 | 10022 | . | . | -15.75 | 194405 |
19440630 | 10022 | . | . | -16.9375 | 194406 |
19440731 | 10022 | . | . | 16.875 | 194407 |
19440831 | 10022 | . | . | -17.125 | 194408 |
19440930 | 10022 | . | . | 18.5 | 194409 |
19441031 | 10022 | 1212 | 0.75 | -16.75 | 194410 |
19441130 | 10022 | . | . | -17.6875 | 194411 |
19441230 | 10022 | . | . | 18.5 | 194412 |
19450131 | 10022 | . | . | 20 | 194501 |
19450228 | 10022 | . | . | -22.0625 | 194502 |
19450331 | 10022 | . | . | 22.5 | 194503 |
19450430 | 10022 | 1212 | 0.75 | 24.375 | 194504 |
19450531 | 10022 | . | . | -24.75 | 194505 |
19450630 | 10022 | . | . | 23.5 | 194506 |
19450731 | 10022 | . | . | -23.375 | 194507 |
19450831 | 10022 | . | . | -24.125 | 194508 |
19450929 | 10022 | . | . | 26 | 194509 |
19451031 | 10022 | 1212 | 0.75 | 28.75 | 194510 |
19451130 | 10022 | . | . | 34 | 194511 |
19451231 | 10022 | . | . | 37.5 | 194512 |
19460131 | 10022 | 1232 | 0.5 | 38 | 194601 |
19460228 | 10022 | . | . | 37.625 | 194602 |
19460330 | 10022 | 1232 | 0.5 | 48 | 194603 |
19460430 | 10022 | . | . | 56.25 | 194604 |
19460531 | 10022 | . | . | 62.5 | 194605 |
19460628 | 10022 | 1232 | 0.5 | 59.5 | 194606 |
19460731 | 10022 | . | . | 56.5 | 194607 |
19460830 | 10022 | 5523 | 0 | 19 | 194608 |
19460930 | 10022 | 1232 | 0.25 | 16.875 | 194609 |
19461031 | 10022 | . | . | 15.25 | 194610 |
19461130 | 10022 | . | . | 15.5 | 194611 |
19461231 | 10022 | 1232 | 0.25 | 14.75 | 194612 |
19461231 | 10022 | 1272 | 0.25 | 14.75 | 194612 |
19470131 | 10022 | . | . | 14.625 | 194701 |
19470228 | 10022 | . | . | 14.625 | 194702 |
19470331 | 10022 | 1232 | 0.25 | 13.875 | 194703 |
19470430 | 10022 | . | . | 12.875 | 194704 |
19470529 | 10022 | . | . | 11.875 | 194705 |
19470630 | 10022 | 1232 | 0.25 | 11.25 | 194706 |
19470731 | 10022 | . | . | 12 | 194707 |
19470829 | 10022 | . | . | 11 | 194708 |
19470930 | 10022 | 1232 | 0.25 | 10.375 | 194709 |
19471031 | 10022 | . | . | 11.25 | 194710 |
19471129 | 10022 | . | . | 10.125 | 194711 |
19471231 | 10022 | . | . | 11 | 194712 |
19480131 | 10022 | 1232 | 0.25 | 11 | 194801 |
19480228 | 10022 | . | . | 10.5 | 194802 |
19480331 | 10022 | . | . | 10.625 | 194803 |
19480430 | 10022 | . | . | 10.25 | 194804 |
19480528 | 10022 | 1232 | 0.25 | 10.625 | 194805 |
19480630 | 10022 | . | . | 10.375 | 194806 |
19480730 | 10022 | . | . | 8.75 | 194807 |
19480831 | 10022 | 1232 | 0.125 | 8.125 | 194808 |
19400831 | 10030 | . | . | -35 | 194008 |
19400930 | 10030 | 1212 | 0.4 | -37.125 | 194009 |
19401031 | 10030 | . | . | 37.5 | 194010 |
19401130 | 10030 | . | . | 38.75 | 194011 |
19401231 | 10030 | 1212 | 0.4 | 38 | 194012 |
19401231 | 10030 | 1272 | 0.6 | 38 | 194012 |
19410131 | 10030 | . | . | 35 | 194101 |
19410228 | 10030 | . | . | 35.125 | 194102 |
19410331 | 10030 | 1212 | 0.4 | -33.5 | 194103 |
19410430 | 10030 | . | . | 31 | 194104 |
19410531 | 10030 | . | . | -35.5 | 194105 |
19410630 | 10030 | 1212 | 0.4 | -36.375 | 194106 |
19410630 | 10030 | 1272 | 0.1 | -36.375 | 194106 |
19410731 | 10030 | . | . | -37.25 | 194107 |
19410830 | 10030 | . | . | -35 | 194108 |
19410930 | 10030 | 1212 | 0.4 | 34 | 194109 |
19410930 | 10030 | 1272 | 0.1 | 34 | 194109 |
19411031 | 10030 | . | . | 32.125 | 194110 |
19411129 | 10030 | . | . | 30 | 194111 |
19411231 | 10030 | 1212 | 0.4 | 28.5 | 194112 |
19411231 | 10030 | 1272 | 0.4 | 28.5 | 194112 |
19420131 | 10030 | . | . | 31.25 | 194201 |
19420228 | 10030 | . | . | -30.4375 | 194202 |
19420331 | 10030 | 1212 | 0.25 | -27.375 | 194203 |
19420430 | 10030 | . | . | -24.6875 | 194204 |
19420529 | 10030 | . | . | -24.5 | 194205 |
19420630 | 10030 | 1212 | 0.25 | 24.75 | 194206 |
19420630 | 10030 | 1272 | 0.1 | 24.75 | 194206 |
19420731 | 10030 | . | . | 25.25 | 194207 |
19420831 | 10030 | . | . | -25.5 | 194208 |
19420930 | 10030 | 1212 | 0.25 | -26.875 | 194209 |
19420930 | 10030 | 1272 | 0.1 | -26.875 | 194209 |
19421031 | 10030 | . | . | 27.5 | 194210 |
19421130 | 10030 | . | . | 28.5 | 194211 |
19421231 | 10030 | 1212 | 0.25 | 27 | 194212 |
19421231 | 10030 | 1272 | 0.5 | 27 | 194212 |
19430130 | 10030 | . | . | 32.625 | 194301 |
19430227 | 10030 | . | . | 34.75 | 194302 |
19430331 | 10030 | 1212 | 0.25 | 38 | 194303 |
19430430 | 10030 | . | . | 37.75 | 194304 |
19430529 | 10030 | . | . | 40.125 | 194305 |
19430630 | 10030 | 1212 | 0.25 | -39.25 | 194306 |
19430630 | 10030 | 1272 | 0.15 | -39.25 | 194306 |
19430731 | 10030 | . | . | 37.5 | 194307 |
19430831 | 10030 | . | . | -36.375 | 194308 |
19430930 | 10030 | 1212 | 0.25 | 37.25 | 194309 |
19430930 | 10030 | 1272 | 0.15 | 37.25 | 194309 |
19431030 | 10030 | . | . | -39.125 | 194310 |
19431130 | 10030 | . | . | 37.25 | 194311 |
19431231 | 10030 | 1212 | 0.25 | -39.5 | 194312 |
19431231 | 10030 | 1272 | 0.5 | -39.5 | 194312 |
19440131 | 10030 | . | . | 40 | 194401 |
19440229 | 10030 | . | . | 42 | 194402 |
19440331 | 10030 | 1212 | 0.25 | 39.25 | 194403 |
19440331 | 10030 | 1272 | 0.1 | 39.25 | 194403 |
19440429 | 10030 | . | . | 39 | 194404 |
19440531 | 10030 | . | . | 40.75 | 194405 |
19440630 | 10030 | 1212 | 0.25 | 44.5 | 194406 |
19440630 | 10030 | 1272 | 0.15 | 44.5 | 194406 |
19440731 | 10030 | . | . | 43 | 194407 |
19440831 | 10030 | . | . | 45.25 | 194408 |
19440930 | 10030 | 1212 | 0.25 | 42.25 | 194409 |
19440930 | 10030 | 1272 | 0.15 | 42.25 | 194409 |
19441031 | 10030 | . | . | 44.25 | 194410 |
19441130 | 10030 | . | . | 45 | 194411 |
19441230 | 10030 | 1212 | 0.25 | 45.75 | 194412 |
19441230 | 10030 | 1272 | 0.25 | 45.75 | 194412 |
19450131 | 10030 | . | . | 44.875 | 194501 |
19450228 | 10030 | . | . | 46.5 | 194502 |
19450331 | 10030 | 1212 | 0.25 | 44 | 194503 |
19450331 | 10030 | 1272 | 0.1 | 44 | 194503 |
19450430 | 10030 | . | . | 46 | 194504 |
19450531 | 10030 | . | . | 44.875 | 194505 |
19450630 | 10030 | 1212 | 0.25 | 44.25 | 194506 |
19450630 | 10030 | 1272 | 0.15 | 44.25 | 194506 |
19450731 | 10030 | . | . | 44 | 194507 |
19450831 | 10030 | . | . | 45 | 194508 |
19450929 | 10030 | 1212 | 0.25 | -51.5 | 194509 |
19450929 | 10030 | 1272 | 0.15 | -51.5 | 194509 |
19451031 | 10030 | . | . | 52.25 | 194510 |
19451130 | 10030 | . | . | 51.5 | 194511 |
19451231 | 10030 | 1212 | 0.25 | 53 | 194512 |
19451231 | 10030 | 1272 | 0.25 | 53 | 194512 |
19460131 | 10030 | . | . | 56 | 194601 |
19460228 | 10030 | . | . | 51 | 194602 |
19460330 | 10030 | 1212 | 0.25 | 52 | 194603 |
19460430 | 10030 | . | . | 62 | 194604 |
19460531 | 10030 | . | . | 61.5 | 194605 |
19460628 | 10030 | 1212 | 0.25 | 63 | 194606 |
19460628 | 10030 | 1272 | 0.25 | 63 | 194606 |
19460731 | 10030 | . | . | 57 | 194607 |
If I understood what you mean.
data have; infile datalines expandtabs; input DATE : yymmdd. PERMNO DISTCD DIVAMT PRC MONTHDATE ; format DATE yymmddn8. ; datalines; 19330531 10014 . . 18 193305 19330630 10014 . . 21.625 193306 19330731 10014 1232 0.15 22.875 193307 19330831 10014 . . 30.25 193308 19330930 10014 . . 26.25 193309 19331031 10014 1232 0.15 25.625 193310 19331031 10014 1272 0.15 25.625 193310 19331129 10014 . . 22.625 193311 19331230 10014 . . 22.375 193312 19340131 10014 1232 0.15 21.75 193401 19340131 10014 1272 0.15 21.75 193401 19340228 10014 . . 19.75 193402 19340331 10014 . . 22.625 193403 19340430 10014 1232 0.15 19.625 193404 19340430 10014 1272 0.15 19.625 193404 19340531 10014 . . 19.5 193405 19340630 10014 . . 21.125 193406 19340731 10014 1232 0.15 19.375 193407 19340731 10014 1272 0.15 19.375 193407 19340831 10014 . . 19 193408 19340929 10014 1232 0.15 20.25 193409 19341031 10014 1272 0.15 17.875 193410 19341130 10014 . . 17.625 193411 19341231 10014 . . 18.875 193412 19350131 10014 1232 0.15 17.375 193501 19350131 10014 1272 0.15 17.375 193501 19350228 10014 . . 16.5 193502 19350330 10014 . . 16.25 193503 19350430 10014 1232 0.15 17.75 193504 19350430 10014 1272 0.15 17.75 193504 19350531 10014 . . 17.25 193505 19350629 10014 . . 16.75 193506 19350731 10014 1232 0.15 15.5 193507 19350731 10014 1272 0.15 15.5 193507 19350831 10014 . . 16.5 193508 19350930 10014 . . 15.5 193509 19351031 10014 1232 0.15 13.875 193510 19351031 10014 1272 0.15 13.875 193510 19351130 10014 . . 15.375 193511 19351231 10014 . . 15.375 193512 19360131 10014 1232 0.15 16.375 193601 19360131 10014 1272 0.15 16.375 193601 19360229 10014 . . 15.5 193602 19360331 10014 . . 15 193603 19360430 10014 1232 0.15 14.375 193604 19360430 10014 1272 0.15 14.375 193604 19360529 10014 . . 14.25 193605 19360630 10014 . . 14.125 193606 19360731 10014 1232 0.15 13.625 193607 19360731 10014 1272 0.15 13.625 193607 19360831 10014 . . 14.625 193608 19360930 10014 . . 16.75 193609 19361031 10014 1232 0.15 15.25 193610 19361031 10014 1272 0.15 15.25 193610 19361130 10014 . . 14.5 193611 19361231 10014 . . 14.625 193612 19370130 10014 1212 0.15 14.5 193701 19370130 10014 1272 0.15 14.5 193701 19370227 10014 . . 15 193702 19370331 10014 . . 14.625 193703 19370430 10014 1212 0.15 12.5 193704 19370430 10014 1272 0.15 12.5 193704 19370528 10014 . . 12 193705 19370630 10014 . . 11.375 193706 19370731 10014 1212 0.15 -11.6875 193707 19370731 10014 1272 0.15 -11.6875 193707 19370831 10014 . . 11.625 193708 19370930 10014 . . 12.25 193709 19371030 10014 1212 0.15 11.375 193710 19371030 10014 1272 0.3 11.375 193710 19371130 10014 . . 11.625 193711 19371231 10014 . . 11.5 193712 19380131 10014 1212 0.15 12.5 193801 19380131 10014 1272 0.15 12.5 193801 19380228 10014 . . 11.75 193802 19380331 10014 . . 8.75 193803 19380430 10014 1212 0.15 9.625 193804 19380430 10014 1272 0.15 9.625 193804 19380531 10014 . . 9.875 193805 19380630 10014 . . 10.5 193806 19380730 10014 1212 0.15 11 193807 19380730 10014 1272 0.15 11 193807 19380831 10014 . . 10.375 193808 19380930 10014 . . 9.875 193809 19381031 10014 1212 0.15 9.5 193810 19381031 10014 1272 0.15 9.5 193810 19381130 10014 . . 9.875 193811 19381231 10014 1232 0.25 9.875 193812 19390131 10014 . . 9.75 193901 19390228 10014 . . 9.75 193902 19390331 10014 . . 8.875 193903 19390429 10014 1232 0.25 8 193904 19390531 10014 . . 8.5 193905 19390630 10014 1232 0.15 7.125 193906 19390731 10014 . . 7.5 193907 19390831 10014 . . 6.875 193908 19390930 10014 . . 6.75 193909 19391031 10014 1232 0.15 6.875 193910 19391130 10014 . . 6.75 193911 19391230 10014 . . 6.375 193912 19400131 10014 1212 0.15 6.625 194001 19400229 10014 . . 6.25 194002 19400330 10014 . . 6.25 194003 19400430 10014 1212 0.15 6.125 194004 19400531 10014 . . 4.625 194005 19400629 10014 . . 4.5 194006 19400731 10014 1212 0.15 4.5 194007 19400831 10014 . . 4.5 194008 19400930 10014 . . 4.75 194009 19401031 10014 1212 0.15 4.75 194010 19401130 10014 . . 5.25 194011 19401231 10014 . . 4.625 194012 19410131 10014 1212 0.125 4.625 194101 19410228 10014 . . 4.5 194102 19410331 10014 . . 4.375 194103 19410430 10014 1212 0.125 4 194104 19410531 10014 . . 4 194105 19410630 10014 . . 4 194106 19410731 10014 1212 0.125 4.375 194107 19410830 10014 . . 4.25 194108 19410930 10014 . . 3.25 194109 19411031 10014 . . 2.5 194110 19411129 10014 . . 2.625 194111 19411231 10014 . . 1.625 194112 19420131 10014 . . 2.375 194201 19420228 10014 . . 2 194202 19420331 10014 . . 1.75 194203 19870227 10016 1232 0.22 71.5 198702 19870331 10016 . . 72 198703 19870430 10016 . . 68 198704 19870529 10016 1232 0.22 67.5 198705 19870630 10016 . . 72.5 198706 19870731 10016 . . 78.5 198707 19870831 10016 1232 0.22 79.75 198708 19870930 10016 . . 74 198709 19871030 10016 . . 45.5 198710 19871130 10016 1232 0.23 49.75 198711 19871231 10016 . . 53.5 198712 19880129 10016 . . 56 198801 19880229 10016 1232 0.23 58.5 198802 19880331 10016 . . 63.5 198803 19880429 10016 . . 70 198804 19880531 10016 1232 0.23 71 198805 19880630 10016 . . 68 198806 19880729 10016 . . 67 198807 19880831 10016 1232 0.23 66.25 198808 19880930 10016 . . 67.25 198809 19881031 10016 . . 79 198810 19881130 10016 1232 0.25 73 198811 19881230 10016 1272 0.2 74 198812 19890131 10016 . . 78 198901 19890228 10016 1232 0.3 78 198902 19890331 10016 . . 78 198903 19890428 10016 . . 74.5 198904 19890531 10016 1232 0.3 74.5 198905 19890630 10016 . . 77 198906 19890731 10016 . . 69 198907 19890831 10016 1232 0.3 77 198908 19890929 10016 . . 78.75 198909 19891031 10016 . . 76 198910 19891130 10016 1232 0.3 -73.5 198911 19891229 10016 . . 65 198912 19900131 10016 5523 0 23 199001 19900228 10016 1232 0.125 24 199002 19900330 10016 . . 23.75 199003 19900430 10016 . . 22.75 199004 19900531 10016 1232 0.125 24.25 199005 19900629 10016 . . 24.75 199006 19900731 10016 . . 23 199007 19900831 10016 1232 0.125 17.5 199008 19900928 10016 . . 15.5 199009 19901031 10016 . . 12 199010 19901130 10016 1232 0.125 12 199011 19901231 10016 . . 13.75 199012 19910131 10016 . . 13.25 199101 19270831 10022 . . 47.375 192708 19270930 10022 5533 0 51.5 192709 19270930 10022 1232 0.75 51.5 192709 19271031 10022 . . 56 192710 19271130 10022 . . 58 192711 19271231 10022 1232 1 58.5 192712 19271231 10022 1272 0.25 58.5 192712 19280131 10022 . . -57.1875 192801 19280229 10022 . . 58 192802 19280331 10022 1232 1 62.5 192803 19280331 10022 1272 0.25 62.5 192803 19280430 10022 . . 64 192804 19280531 10022 . . 67.625 192805 19280630 10022 1232 1 64 192806 19280630 10022 1272 0.25 64 192806 19280731 10022 . . 63 192807 19280831 10022 . . 69.125 192808 19280929 10022 1232 1 71 192809 19280929 10022 1272 0.25 71 192809 19281031 10022 . . 73.5 192810 19281130 10022 . . 72.5 192811 19281231 10022 1232 1 68 192812 19281231 10022 1272 0.25 68 192812 19290131 10022 . . 73.75 192901 19290228 10022 . . 69 192902 19290328 10022 1232 1 65 192903 19290328 10022 1272 0.25 65 192903 19290430 10022 . . 67 192904 19290531 10022 . . 64.75 192905 19290629 10022 1232 1 . 192906 19290629 10022 1272 0.25 . 192906 19290731 10022 . . 65.5 192907 19290830 10022 . . 69 192908 19290930 10022 1232 1 -64.75 192909 19290930 10022 1272 0.25 -64.75 192909 19291031 10022 . . 57 192910 19291127 10022 . . 57.375 192911 19291231 10022 1232 1.25 59 192912 19300131 10022 . . 62.375 193001 19300228 10022 . . 63.375 193002 19300331 10022 1232 1.25 62.5 193003 19300430 10022 . . 66.875 193004 19300529 10022 . . -65.3125 193005 19300630 10022 1232 1.25 58.875 193006 19300731 10022 . . 62.375 193007 19300829 10022 . . 63.75 193008 19300930 10022 1232 1.25 61 193009 19301031 10022 . . 59.25 193010 19301129 10022 . . -58.75 193011 19301231 10022 1232 1.25 58.5 193012 19310131 10022 . . 62.75 193101 19310228 10022 . . 64.375 193102 19310331 10022 1232 1.25 -57.0625 193103 19310430 10022 . . 49.75 193104 19310529 10022 . . 49 193105 19310630 10022 1232 1.25 49 193106 19310731 10022 . . 49.75 193107 19310831 10022 . . 48.375 193108 19310930 10022 1232 1.25 30 193109 19311031 10022 . . 34.5 193110 19311130 10022 . . 31 193111 19311231 10022 1232 1.25 23.5 193112 19320130 10022 . . -22.375 193201 19320229 10022 . . 25.5 193202 19320331 10022 1232 0.75 22.25 193203 19320430 10022 . . -18.0625 193204 19320531 10022 . . 14.5 193205 19320630 10022 1232 0.75 15 193206 19320730 10022 . . -20.6875 193207 19320831 10022 . . 23.25 193208 19320930 10022 1232 0.75 -22.625 193209 19321031 10022 . . -20 193210 19321130 10022 . . . 193211 19321231 10022 1232 0.75 22.875 193212 19330131 10022 . . 21.5 193301 19340731 10022 . . 52.5 193407 19340831 10022 . . -54.75 193408 19340929 10022 1232 1 -54.25 193409 19341031 10022 . . -54.625 193410 19341130 10022 . . 63.25 193411 19341231 10022 1232 1 68 193412 19350131 10022 . . -69.6875 193501 19350228 10022 . . 74.875 193502 19350330 10022 1232 1 -67.375 193503 19350330 10022 1272 0.25 -67.375 193503 19350430 10022 . . 74 193504 19350531 10022 . . 76.625 193505 19350629 10022 1232 1.25 -82.75 193506 19350731 10022 . . 93 193507 19350831 10022 . . -89.5 193508 19350930 10022 1232 1.25 94 193509 19351031 10022 . . -91 193510 19351130 10022 . . 94 193511 19351231 10022 1232 1.25 91 193512 19360131 10022 . . -94 193601 19360229 10022 . . -111.625 193602 19360331 10022 1232 1.5 -109.75 193603 19360430 10022 . . 99 193604 19360529 10022 . . -101.75 193605 19360630 10022 1232 1.5 -108.625 193606 19360731 10022 . . -107.6875 193607 19360831 10022 5523 0 38.5 193608 19360930 10022 1232 0.5 -37.25 193609 19361031 10022 . . 37.75 193610 19361130 10022 1232 0.5 36 193611 19361130 10022 1272 0.25 36 193611 19361231 10022 . . 33.25 193612 19370130 10022 . . 34.75 193701 19370227 10022 . . 34 193702 19370331 10022 1212 0.5 31.875 193703 19370430 10022 . . 29.5 193704 19370528 10022 . . -29.625 193705 19370630 10022 1212 0.5 27.75 193706 19370731 10022 . . -29.25 193707 19370831 10022 . . 27 193708 19370930 10022 1212 0.5 24.25 193709 19371030 10022 . . 23 193710 19371130 10022 . . 18.5 193711 19371231 10022 1212 0.5 15.75 193712 19380131 10022 . . -19.5625 193801 19380228 10022 . . 19.5 193802 19380331 10022 1212 0.5 15.375 193803 19380430 10022 . . -17.25 193804 19380531 10022 . . 16.75 193805 19380630 10022 1212 0.4 17 193806 19380730 10022 . . 18.5 193807 19380831 10022 . . -17.875 193808 19380930 10022 1212 0.4 17.25 193809 19381031 10022 . . 16.875 193810 19381130 10022 . . 16.25 193811 19381231 10022 1212 0.4 14.875 193812 19420331 10022 . . 5.125 194203 19420430 10022 1212 0.25 5.25 194204 19420529 10022 . . 5.875 194205 19420630 10022 . . -5.8125 194206 19420731 10022 . . 6.375 194207 19420831 10022 . . -7.1875 194208 19420930 10022 . . 7.5 194209 19421031 10022 1212 0.25 -7.75 194210 19421130 10022 . . 9.25 194211 19421231 10022 1272 0.25 -9.1875 194212 19430130 10022 . . 9.625 194301 19430227 10022 . . 13 194302 19430331 10022 . . 14 194303 19430430 10022 1212 0.5 13.875 194304 19430529 10022 . . 14.25 194305 19430630 10022 . . 14.25 194306 19430731 10022 . . 13.75 194307 19430831 10022 . . -13.625 194308 19430930 10022 . . 14 194309 19431030 10022 1212 0.5 -14.25 194310 19431130 10022 . . -13.875 194311 19431231 10022 . . 13.75 194312 19440131 10022 . . 14.5 194401 19440229 10022 . . -14.625 194402 19440331 10022 . . 16 194403 19440429 10022 1212 0.5 -15.375 194404 19440531 10022 . . -15.75 194405 19440630 10022 . . -16.9375 194406 19440731 10022 . . 16.875 194407 19440831 10022 . . -17.125 194408 19440930 10022 . . 18.5 194409 19441031 10022 1212 0.75 -16.75 194410 19441130 10022 . . -17.6875 194411 19441230 10022 . . 18.5 194412 19450131 10022 . . 20 194501 19450228 10022 . . -22.0625 194502 19450331 10022 . . 22.5 194503 19450430 10022 1212 0.75 24.375 194504 19450531 10022 . . -24.75 194505 19450630 10022 . . 23.5 194506 19450731 10022 . . -23.375 194507 19450831 10022 . . -24.125 194508 19450929 10022 . . 26 194509 19451031 10022 1212 0.75 28.75 194510 19451130 10022 . . 34 194511 19451231 10022 . . 37.5 194512 19460131 10022 1232 0.5 38 194601 19460228 10022 . . 37.625 194602 19460330 10022 1232 0.5 48 194603 19460430 10022 . . 56.25 194604 19460531 10022 . . 62.5 194605 19460628 10022 1232 0.5 59.5 194606 19460731 10022 . . 56.5 194607 19460830 10022 5523 0 19 194608 19460930 10022 1232 0.25 16.875 194609 19461031 10022 . . 15.25 194610 19461130 10022 . . 15.5 194611 19461231 10022 1232 0.25 14.75 194612 19461231 10022 1272 0.25 14.75 194612 19470131 10022 . . 14.625 194701 19470228 10022 . . 14.625 194702 19470331 10022 1232 0.25 13.875 194703 19470430 10022 . . 12.875 194704 19470529 10022 . . 11.875 194705 19470630 10022 1232 0.25 11.25 194706 19470731 10022 . . 12 194707 19470829 10022 . . 11 194708 19470930 10022 1232 0.25 10.375 194709 19471031 10022 . . 11.25 194710 19471129 10022 . . 10.125 194711 19471231 10022 . . 11 194712 19480131 10022 1232 0.25 11 194801 19480228 10022 . . 10.5 194802 19480331 10022 . . 10.625 194803 19480430 10022 . . 10.25 194804 19480528 10022 1232 0.25 10.625 194805 19480630 10022 . . 10.375 194806 19480730 10022 . . 8.75 194807 19480831 10022 1232 0.125 8.125 194808 19400831 10030 . . -35 194008 19400930 10030 1212 0.4 -37.125 194009 19401031 10030 . . 37.5 194010 19401130 10030 . . 38.75 194011 19401231 10030 1212 0.4 38 194012 19401231 10030 1272 0.6 38 194012 19410131 10030 . . 35 194101 19410228 10030 . . 35.125 194102 19410331 10030 1212 0.4 -33.5 194103 19410430 10030 . . 31 194104 19410531 10030 . . -35.5 194105 19410630 10030 1212 0.4 -36.375 194106 19410630 10030 1272 0.1 -36.375 194106 19410731 10030 . . -37.25 194107 19410830 10030 . . -35 194108 19410930 10030 1212 0.4 34 194109 19410930 10030 1272 0.1 34 194109 19411031 10030 . . 32.125 194110 19411129 10030 . . 30 194111 19411231 10030 1212 0.4 28.5 194112 19411231 10030 1272 0.4 28.5 194112 19420131 10030 . . 31.25 194201 19420228 10030 . . -30.4375 194202 19420331 10030 1212 0.25 -27.375 194203 19420430 10030 . . -24.6875 194204 19420529 10030 . . -24.5 194205 19420630 10030 1212 0.25 24.75 194206 19420630 10030 1272 0.1 24.75 194206 19420731 10030 . . 25.25 194207 19420831 10030 . . -25.5 194208 19420930 10030 1212 0.25 -26.875 194209 19420930 10030 1272 0.1 -26.875 194209 19421031 10030 . . 27.5 194210 19421130 10030 . . 28.5 194211 19421231 10030 1212 0.25 27 194212 19421231 10030 1272 0.5 27 194212 19430130 10030 . . 32.625 194301 19430227 10030 . . 34.75 194302 19430331 10030 1212 0.25 38 194303 19430430 10030 . . 37.75 194304 19430529 10030 . . 40.125 194305 19430630 10030 1212 0.25 -39.25 194306 19430630 10030 1272 0.15 -39.25 194306 19430731 10030 . . 37.5 194307 19430831 10030 . . -36.375 194308 19430930 10030 1212 0.25 37.25 194309 19430930 10030 1272 0.15 37.25 194309 19431030 10030 . . -39.125 194310 19431130 10030 . . 37.25 194311 19431231 10030 1212 0.25 -39.5 194312 19431231 10030 1272 0.5 -39.5 194312 19440131 10030 . . 40 194401 19440229 10030 . . 42 194402 19440331 10030 1212 0.25 39.25 194403 19440331 10030 1272 0.1 39.25 194403 19440429 10030 . . 39 194404 19440531 10030 . . 40.75 194405 19440630 10030 1212 0.25 44.5 194406 19440630 10030 1272 0.15 44.5 194406 19440731 10030 . . 43 194407 19440831 10030 . . 45.25 194408 19440930 10030 1212 0.25 42.25 194409 19440930 10030 1272 0.15 42.25 194409 19441031 10030 . . 44.25 194410 19441130 10030 . . 45 194411 19441230 10030 1212 0.25 45.75 194412 19441230 10030 1272 0.25 45.75 194412 19450131 10030 . . 44.875 194501 19450228 10030 . . 46.5 194502 19450331 10030 1212 0.25 44 194503 19450331 10030 1272 0.1 44 194503 19450430 10030 . . 46 194504 19450531 10030 . . 44.875 194505 19450630 10030 1212 0.25 44.25 194506 19450630 10030 1272 0.15 44.25 194506 19450731 10030 . . 44 194507 19450831 10030 . . 45 194508 19450929 10030 1212 0.25 -51.5 194509 19450929 10030 1272 0.15 -51.5 194509 19451031 10030 . . 52.25 194510 19451130 10030 . . 51.5 194511 19451231 10030 1212 0.25 53 194512 19451231 10030 1272 0.25 53 194512 19460131 10030 . . 56 194601 19460228 10030 . . 51 194602 19460330 10030 1212 0.25 52 194603 19460430 10030 . . 62 194604 19460531 10030 . . 61.5 194605 19460628 10030 1212 0.25 63 194606 19460628 10030 1272 0.25 63 194606 19460731 10030 . . 57 194607 ; run; %let list= 1262 1272 ; proc sql; create table temp as select permno,date,DISTCD,intnx('year',date,-2,'s') as prev_two_year format=yymmddn8.,intnx('year',date,2,'s') as next_two_year format=yymmddn8. from have where DISTCD in ( &list ) order by permno,date; quit; data group; set temp; if permno ne lag(permno) or (permno eq lag(permno) and prev_two_year gt lag(next_two_year)) then group+1; run; data date_range; set group; by group; retain start; if first.group then start=prev_two_year; if last.group then do;end=next_two_year; output;end; keep permno start end; format start end yymmddn8.; run; proc sql; create table want as select * from have except select a.* from have as a,date_range as b where a.permno=b.permno and a.date between b.start and b.end ; quit;
Ksharp
The data you've posted don't match your narrative. There is neither a permno= 93417 nor a distcd= 1262.
Suggest you align data and text (eg. are in the aligne data also all these missings?) and also best post your data as csv attachement.
The selection your after should be doable with a single SQL statement and then a data step to create the 3 output datasets you're after.
I don't understand why you need this month variable. You might want read a bit about SAS dates, formats and informats and the intnx() calendar function.
The narrative was hypothetical. If you would like a real example it is much the same.....
For example:keep monthdate 193309 for permno 10014 (one month prior), as distcd 1272 exists in 193310 for permno 10014.
Yes I have dabbled with SAS dates but I much prefer to keep my dates as integers as the numbers are convenient to add, subtract, multiply, divide as I see fit.
The SQL sounds promising, as requested a sample of my data is attached in CSV format
Thus the earliest records for each id go into the third dataset? What do you want to do if data for a previous month are missing? What do you want to do if a previous month's PRC is a negative value? Also, how do you want to define previous month? e.g., previous calendar month?
Sorry this is an error with the data source from CRSP. A firms stock price cannot be negative. Disregard or delete any negative PRC.
Those 1262 and 1272 distributions with no 1 month prior PRC (missing or negative) delete, they cant be allocated into any dataset.
Previous month is previous calender month, the column monthdate provides the source to refer back to.
The third dataset should just contain the 1262 and 1272 observations, which:
1) Has a previous calender month PRC to compare the current DIVAMT to
2) The DIVAMT is less than 5% of previous months PRC
Not sure if I correctly understand what you are trying to do but, if not, I think you should be able to modify the following code to accomplish what you want:
data want1 want2 want3;
set sample;
by permno monthdate;
retain last_date last_prc;
if prc gt 0 then do;
if first.monthdate then do;
last_date=lag(date);
last_prc=lag(prc);
end;
if distcd in (1262,1272) then do;
if intck('month',input(put(last_date,8.),yymmdd8.),input(put(date,8.),yymmdd8.)) eq 1
then do;
if divamt/last_prc ge 0.1 then output want1;
else if divamt/last_prc ge 0.05 then output want2;
else output want3;
end;
else output want3;
end;
end;
run;
Looking at your data I understand the key is {permno,date}. Is this correct?
If so then the first thing to do is cleaning up the data as there would be duplicates.
proc sql;
create table DupKeys as
select *, count(*) as dups
from sample
group by permno,date
having count(*)>1
;
quit;
If I understood the key right then once your data is cleaned up below SQL creates a table with all rows having a non missing distcd in the current month and a missing distcd in the previous month (the lag1_<variable name> come from the previous month).
I believe once you've got this result table it's fairly simple to split it up in the way you want to as you can do all selections on row level in a data step.
I've added variable MonthEndDate which always contains the date of the last day of the month (your date variable seems to be a bit inconsistent in this regard).
proc sql;
create table want as
select
intnx('month',input(put(l.date,8.),yymmdd8.),0,'e') as MonthEndDate format=yymmdd10.,
l.*,
r.date as lag1_date,
r.prc as lag1_prc
from sample l,
(select * from sample where distcd is null) r
where l.permno=r.permno
and intck('month',input(put(r.date,8.),yymmdd8.),input(put(l.date,8.),yymmdd8.))=1
and l.distcd not is null
order by l.permno, l.date
;
quit;
And to insist a bit more of how to deal with dates:
All people with SAS experience here in this communities use SAS dates and not date strings (whether they are now stored in a numeric or a character variable) when it comes to any calculations with dates. There must be a reason for this....
Thanks everyone.
In a new separate step, I am looking to perform the following:
For all those permnos which were linked to a 1262 or 1272.... I would like to delete the observations two years before and two years after the 1262 or 1272.
EG: Permno 10022 in 19421231 had a 1272. I would like to delete any observation of 10022 from 19401231 to 19441231.
If I understood what you mean.
data have; infile datalines expandtabs; input DATE : yymmdd. PERMNO DISTCD DIVAMT PRC MONTHDATE ; format DATE yymmddn8. ; datalines; 19330531 10014 . . 18 193305 19330630 10014 . . 21.625 193306 19330731 10014 1232 0.15 22.875 193307 19330831 10014 . . 30.25 193308 19330930 10014 . . 26.25 193309 19331031 10014 1232 0.15 25.625 193310 19331031 10014 1272 0.15 25.625 193310 19331129 10014 . . 22.625 193311 19331230 10014 . . 22.375 193312 19340131 10014 1232 0.15 21.75 193401 19340131 10014 1272 0.15 21.75 193401 19340228 10014 . . 19.75 193402 19340331 10014 . . 22.625 193403 19340430 10014 1232 0.15 19.625 193404 19340430 10014 1272 0.15 19.625 193404 19340531 10014 . . 19.5 193405 19340630 10014 . . 21.125 193406 19340731 10014 1232 0.15 19.375 193407 19340731 10014 1272 0.15 19.375 193407 19340831 10014 . . 19 193408 19340929 10014 1232 0.15 20.25 193409 19341031 10014 1272 0.15 17.875 193410 19341130 10014 . . 17.625 193411 19341231 10014 . . 18.875 193412 19350131 10014 1232 0.15 17.375 193501 19350131 10014 1272 0.15 17.375 193501 19350228 10014 . . 16.5 193502 19350330 10014 . . 16.25 193503 19350430 10014 1232 0.15 17.75 193504 19350430 10014 1272 0.15 17.75 193504 19350531 10014 . . 17.25 193505 19350629 10014 . . 16.75 193506 19350731 10014 1232 0.15 15.5 193507 19350731 10014 1272 0.15 15.5 193507 19350831 10014 . . 16.5 193508 19350930 10014 . . 15.5 193509 19351031 10014 1232 0.15 13.875 193510 19351031 10014 1272 0.15 13.875 193510 19351130 10014 . . 15.375 193511 19351231 10014 . . 15.375 193512 19360131 10014 1232 0.15 16.375 193601 19360131 10014 1272 0.15 16.375 193601 19360229 10014 . . 15.5 193602 19360331 10014 . . 15 193603 19360430 10014 1232 0.15 14.375 193604 19360430 10014 1272 0.15 14.375 193604 19360529 10014 . . 14.25 193605 19360630 10014 . . 14.125 193606 19360731 10014 1232 0.15 13.625 193607 19360731 10014 1272 0.15 13.625 193607 19360831 10014 . . 14.625 193608 19360930 10014 . . 16.75 193609 19361031 10014 1232 0.15 15.25 193610 19361031 10014 1272 0.15 15.25 193610 19361130 10014 . . 14.5 193611 19361231 10014 . . 14.625 193612 19370130 10014 1212 0.15 14.5 193701 19370130 10014 1272 0.15 14.5 193701 19370227 10014 . . 15 193702 19370331 10014 . . 14.625 193703 19370430 10014 1212 0.15 12.5 193704 19370430 10014 1272 0.15 12.5 193704 19370528 10014 . . 12 193705 19370630 10014 . . 11.375 193706 19370731 10014 1212 0.15 -11.6875 193707 19370731 10014 1272 0.15 -11.6875 193707 19370831 10014 . . 11.625 193708 19370930 10014 . . 12.25 193709 19371030 10014 1212 0.15 11.375 193710 19371030 10014 1272 0.3 11.375 193710 19371130 10014 . . 11.625 193711 19371231 10014 . . 11.5 193712 19380131 10014 1212 0.15 12.5 193801 19380131 10014 1272 0.15 12.5 193801 19380228 10014 . . 11.75 193802 19380331 10014 . . 8.75 193803 19380430 10014 1212 0.15 9.625 193804 19380430 10014 1272 0.15 9.625 193804 19380531 10014 . . 9.875 193805 19380630 10014 . . 10.5 193806 19380730 10014 1212 0.15 11 193807 19380730 10014 1272 0.15 11 193807 19380831 10014 . . 10.375 193808 19380930 10014 . . 9.875 193809 19381031 10014 1212 0.15 9.5 193810 19381031 10014 1272 0.15 9.5 193810 19381130 10014 . . 9.875 193811 19381231 10014 1232 0.25 9.875 193812 19390131 10014 . . 9.75 193901 19390228 10014 . . 9.75 193902 19390331 10014 . . 8.875 193903 19390429 10014 1232 0.25 8 193904 19390531 10014 . . 8.5 193905 19390630 10014 1232 0.15 7.125 193906 19390731 10014 . . 7.5 193907 19390831 10014 . . 6.875 193908 19390930 10014 . . 6.75 193909 19391031 10014 1232 0.15 6.875 193910 19391130 10014 . . 6.75 193911 19391230 10014 . . 6.375 193912 19400131 10014 1212 0.15 6.625 194001 19400229 10014 . . 6.25 194002 19400330 10014 . . 6.25 194003 19400430 10014 1212 0.15 6.125 194004 19400531 10014 . . 4.625 194005 19400629 10014 . . 4.5 194006 19400731 10014 1212 0.15 4.5 194007 19400831 10014 . . 4.5 194008 19400930 10014 . . 4.75 194009 19401031 10014 1212 0.15 4.75 194010 19401130 10014 . . 5.25 194011 19401231 10014 . . 4.625 194012 19410131 10014 1212 0.125 4.625 194101 19410228 10014 . . 4.5 194102 19410331 10014 . . 4.375 194103 19410430 10014 1212 0.125 4 194104 19410531 10014 . . 4 194105 19410630 10014 . . 4 194106 19410731 10014 1212 0.125 4.375 194107 19410830 10014 . . 4.25 194108 19410930 10014 . . 3.25 194109 19411031 10014 . . 2.5 194110 19411129 10014 . . 2.625 194111 19411231 10014 . . 1.625 194112 19420131 10014 . . 2.375 194201 19420228 10014 . . 2 194202 19420331 10014 . . 1.75 194203 19870227 10016 1232 0.22 71.5 198702 19870331 10016 . . 72 198703 19870430 10016 . . 68 198704 19870529 10016 1232 0.22 67.5 198705 19870630 10016 . . 72.5 198706 19870731 10016 . . 78.5 198707 19870831 10016 1232 0.22 79.75 198708 19870930 10016 . . 74 198709 19871030 10016 . . 45.5 198710 19871130 10016 1232 0.23 49.75 198711 19871231 10016 . . 53.5 198712 19880129 10016 . . 56 198801 19880229 10016 1232 0.23 58.5 198802 19880331 10016 . . 63.5 198803 19880429 10016 . . 70 198804 19880531 10016 1232 0.23 71 198805 19880630 10016 . . 68 198806 19880729 10016 . . 67 198807 19880831 10016 1232 0.23 66.25 198808 19880930 10016 . . 67.25 198809 19881031 10016 . . 79 198810 19881130 10016 1232 0.25 73 198811 19881230 10016 1272 0.2 74 198812 19890131 10016 . . 78 198901 19890228 10016 1232 0.3 78 198902 19890331 10016 . . 78 198903 19890428 10016 . . 74.5 198904 19890531 10016 1232 0.3 74.5 198905 19890630 10016 . . 77 198906 19890731 10016 . . 69 198907 19890831 10016 1232 0.3 77 198908 19890929 10016 . . 78.75 198909 19891031 10016 . . 76 198910 19891130 10016 1232 0.3 -73.5 198911 19891229 10016 . . 65 198912 19900131 10016 5523 0 23 199001 19900228 10016 1232 0.125 24 199002 19900330 10016 . . 23.75 199003 19900430 10016 . . 22.75 199004 19900531 10016 1232 0.125 24.25 199005 19900629 10016 . . 24.75 199006 19900731 10016 . . 23 199007 19900831 10016 1232 0.125 17.5 199008 19900928 10016 . . 15.5 199009 19901031 10016 . . 12 199010 19901130 10016 1232 0.125 12 199011 19901231 10016 . . 13.75 199012 19910131 10016 . . 13.25 199101 19270831 10022 . . 47.375 192708 19270930 10022 5533 0 51.5 192709 19270930 10022 1232 0.75 51.5 192709 19271031 10022 . . 56 192710 19271130 10022 . . 58 192711 19271231 10022 1232 1 58.5 192712 19271231 10022 1272 0.25 58.5 192712 19280131 10022 . . -57.1875 192801 19280229 10022 . . 58 192802 19280331 10022 1232 1 62.5 192803 19280331 10022 1272 0.25 62.5 192803 19280430 10022 . . 64 192804 19280531 10022 . . 67.625 192805 19280630 10022 1232 1 64 192806 19280630 10022 1272 0.25 64 192806 19280731 10022 . . 63 192807 19280831 10022 . . 69.125 192808 19280929 10022 1232 1 71 192809 19280929 10022 1272 0.25 71 192809 19281031 10022 . . 73.5 192810 19281130 10022 . . 72.5 192811 19281231 10022 1232 1 68 192812 19281231 10022 1272 0.25 68 192812 19290131 10022 . . 73.75 192901 19290228 10022 . . 69 192902 19290328 10022 1232 1 65 192903 19290328 10022 1272 0.25 65 192903 19290430 10022 . . 67 192904 19290531 10022 . . 64.75 192905 19290629 10022 1232 1 . 192906 19290629 10022 1272 0.25 . 192906 19290731 10022 . . 65.5 192907 19290830 10022 . . 69 192908 19290930 10022 1232 1 -64.75 192909 19290930 10022 1272 0.25 -64.75 192909 19291031 10022 . . 57 192910 19291127 10022 . . 57.375 192911 19291231 10022 1232 1.25 59 192912 19300131 10022 . . 62.375 193001 19300228 10022 . . 63.375 193002 19300331 10022 1232 1.25 62.5 193003 19300430 10022 . . 66.875 193004 19300529 10022 . . -65.3125 193005 19300630 10022 1232 1.25 58.875 193006 19300731 10022 . . 62.375 193007 19300829 10022 . . 63.75 193008 19300930 10022 1232 1.25 61 193009 19301031 10022 . . 59.25 193010 19301129 10022 . . -58.75 193011 19301231 10022 1232 1.25 58.5 193012 19310131 10022 . . 62.75 193101 19310228 10022 . . 64.375 193102 19310331 10022 1232 1.25 -57.0625 193103 19310430 10022 . . 49.75 193104 19310529 10022 . . 49 193105 19310630 10022 1232 1.25 49 193106 19310731 10022 . . 49.75 193107 19310831 10022 . . 48.375 193108 19310930 10022 1232 1.25 30 193109 19311031 10022 . . 34.5 193110 19311130 10022 . . 31 193111 19311231 10022 1232 1.25 23.5 193112 19320130 10022 . . -22.375 193201 19320229 10022 . . 25.5 193202 19320331 10022 1232 0.75 22.25 193203 19320430 10022 . . -18.0625 193204 19320531 10022 . . 14.5 193205 19320630 10022 1232 0.75 15 193206 19320730 10022 . . -20.6875 193207 19320831 10022 . . 23.25 193208 19320930 10022 1232 0.75 -22.625 193209 19321031 10022 . . -20 193210 19321130 10022 . . . 193211 19321231 10022 1232 0.75 22.875 193212 19330131 10022 . . 21.5 193301 19340731 10022 . . 52.5 193407 19340831 10022 . . -54.75 193408 19340929 10022 1232 1 -54.25 193409 19341031 10022 . . -54.625 193410 19341130 10022 . . 63.25 193411 19341231 10022 1232 1 68 193412 19350131 10022 . . -69.6875 193501 19350228 10022 . . 74.875 193502 19350330 10022 1232 1 -67.375 193503 19350330 10022 1272 0.25 -67.375 193503 19350430 10022 . . 74 193504 19350531 10022 . . 76.625 193505 19350629 10022 1232 1.25 -82.75 193506 19350731 10022 . . 93 193507 19350831 10022 . . -89.5 193508 19350930 10022 1232 1.25 94 193509 19351031 10022 . . -91 193510 19351130 10022 . . 94 193511 19351231 10022 1232 1.25 91 193512 19360131 10022 . . -94 193601 19360229 10022 . . -111.625 193602 19360331 10022 1232 1.5 -109.75 193603 19360430 10022 . . 99 193604 19360529 10022 . . -101.75 193605 19360630 10022 1232 1.5 -108.625 193606 19360731 10022 . . -107.6875 193607 19360831 10022 5523 0 38.5 193608 19360930 10022 1232 0.5 -37.25 193609 19361031 10022 . . 37.75 193610 19361130 10022 1232 0.5 36 193611 19361130 10022 1272 0.25 36 193611 19361231 10022 . . 33.25 193612 19370130 10022 . . 34.75 193701 19370227 10022 . . 34 193702 19370331 10022 1212 0.5 31.875 193703 19370430 10022 . . 29.5 193704 19370528 10022 . . -29.625 193705 19370630 10022 1212 0.5 27.75 193706 19370731 10022 . . -29.25 193707 19370831 10022 . . 27 193708 19370930 10022 1212 0.5 24.25 193709 19371030 10022 . . 23 193710 19371130 10022 . . 18.5 193711 19371231 10022 1212 0.5 15.75 193712 19380131 10022 . . -19.5625 193801 19380228 10022 . . 19.5 193802 19380331 10022 1212 0.5 15.375 193803 19380430 10022 . . -17.25 193804 19380531 10022 . . 16.75 193805 19380630 10022 1212 0.4 17 193806 19380730 10022 . . 18.5 193807 19380831 10022 . . -17.875 193808 19380930 10022 1212 0.4 17.25 193809 19381031 10022 . . 16.875 193810 19381130 10022 . . 16.25 193811 19381231 10022 1212 0.4 14.875 193812 19420331 10022 . . 5.125 194203 19420430 10022 1212 0.25 5.25 194204 19420529 10022 . . 5.875 194205 19420630 10022 . . -5.8125 194206 19420731 10022 . . 6.375 194207 19420831 10022 . . -7.1875 194208 19420930 10022 . . 7.5 194209 19421031 10022 1212 0.25 -7.75 194210 19421130 10022 . . 9.25 194211 19421231 10022 1272 0.25 -9.1875 194212 19430130 10022 . . 9.625 194301 19430227 10022 . . 13 194302 19430331 10022 . . 14 194303 19430430 10022 1212 0.5 13.875 194304 19430529 10022 . . 14.25 194305 19430630 10022 . . 14.25 194306 19430731 10022 . . 13.75 194307 19430831 10022 . . -13.625 194308 19430930 10022 . . 14 194309 19431030 10022 1212 0.5 -14.25 194310 19431130 10022 . . -13.875 194311 19431231 10022 . . 13.75 194312 19440131 10022 . . 14.5 194401 19440229 10022 . . -14.625 194402 19440331 10022 . . 16 194403 19440429 10022 1212 0.5 -15.375 194404 19440531 10022 . . -15.75 194405 19440630 10022 . . -16.9375 194406 19440731 10022 . . 16.875 194407 19440831 10022 . . -17.125 194408 19440930 10022 . . 18.5 194409 19441031 10022 1212 0.75 -16.75 194410 19441130 10022 . . -17.6875 194411 19441230 10022 . . 18.5 194412 19450131 10022 . . 20 194501 19450228 10022 . . -22.0625 194502 19450331 10022 . . 22.5 194503 19450430 10022 1212 0.75 24.375 194504 19450531 10022 . . -24.75 194505 19450630 10022 . . 23.5 194506 19450731 10022 . . -23.375 194507 19450831 10022 . . -24.125 194508 19450929 10022 . . 26 194509 19451031 10022 1212 0.75 28.75 194510 19451130 10022 . . 34 194511 19451231 10022 . . 37.5 194512 19460131 10022 1232 0.5 38 194601 19460228 10022 . . 37.625 194602 19460330 10022 1232 0.5 48 194603 19460430 10022 . . 56.25 194604 19460531 10022 . . 62.5 194605 19460628 10022 1232 0.5 59.5 194606 19460731 10022 . . 56.5 194607 19460830 10022 5523 0 19 194608 19460930 10022 1232 0.25 16.875 194609 19461031 10022 . . 15.25 194610 19461130 10022 . . 15.5 194611 19461231 10022 1232 0.25 14.75 194612 19461231 10022 1272 0.25 14.75 194612 19470131 10022 . . 14.625 194701 19470228 10022 . . 14.625 194702 19470331 10022 1232 0.25 13.875 194703 19470430 10022 . . 12.875 194704 19470529 10022 . . 11.875 194705 19470630 10022 1232 0.25 11.25 194706 19470731 10022 . . 12 194707 19470829 10022 . . 11 194708 19470930 10022 1232 0.25 10.375 194709 19471031 10022 . . 11.25 194710 19471129 10022 . . 10.125 194711 19471231 10022 . . 11 194712 19480131 10022 1232 0.25 11 194801 19480228 10022 . . 10.5 194802 19480331 10022 . . 10.625 194803 19480430 10022 . . 10.25 194804 19480528 10022 1232 0.25 10.625 194805 19480630 10022 . . 10.375 194806 19480730 10022 . . 8.75 194807 19480831 10022 1232 0.125 8.125 194808 19400831 10030 . . -35 194008 19400930 10030 1212 0.4 -37.125 194009 19401031 10030 . . 37.5 194010 19401130 10030 . . 38.75 194011 19401231 10030 1212 0.4 38 194012 19401231 10030 1272 0.6 38 194012 19410131 10030 . . 35 194101 19410228 10030 . . 35.125 194102 19410331 10030 1212 0.4 -33.5 194103 19410430 10030 . . 31 194104 19410531 10030 . . -35.5 194105 19410630 10030 1212 0.4 -36.375 194106 19410630 10030 1272 0.1 -36.375 194106 19410731 10030 . . -37.25 194107 19410830 10030 . . -35 194108 19410930 10030 1212 0.4 34 194109 19410930 10030 1272 0.1 34 194109 19411031 10030 . . 32.125 194110 19411129 10030 . . 30 194111 19411231 10030 1212 0.4 28.5 194112 19411231 10030 1272 0.4 28.5 194112 19420131 10030 . . 31.25 194201 19420228 10030 . . -30.4375 194202 19420331 10030 1212 0.25 -27.375 194203 19420430 10030 . . -24.6875 194204 19420529 10030 . . -24.5 194205 19420630 10030 1212 0.25 24.75 194206 19420630 10030 1272 0.1 24.75 194206 19420731 10030 . . 25.25 194207 19420831 10030 . . -25.5 194208 19420930 10030 1212 0.25 -26.875 194209 19420930 10030 1272 0.1 -26.875 194209 19421031 10030 . . 27.5 194210 19421130 10030 . . 28.5 194211 19421231 10030 1212 0.25 27 194212 19421231 10030 1272 0.5 27 194212 19430130 10030 . . 32.625 194301 19430227 10030 . . 34.75 194302 19430331 10030 1212 0.25 38 194303 19430430 10030 . . 37.75 194304 19430529 10030 . . 40.125 194305 19430630 10030 1212 0.25 -39.25 194306 19430630 10030 1272 0.15 -39.25 194306 19430731 10030 . . 37.5 194307 19430831 10030 . . -36.375 194308 19430930 10030 1212 0.25 37.25 194309 19430930 10030 1272 0.15 37.25 194309 19431030 10030 . . -39.125 194310 19431130 10030 . . 37.25 194311 19431231 10030 1212 0.25 -39.5 194312 19431231 10030 1272 0.5 -39.5 194312 19440131 10030 . . 40 194401 19440229 10030 . . 42 194402 19440331 10030 1212 0.25 39.25 194403 19440331 10030 1272 0.1 39.25 194403 19440429 10030 . . 39 194404 19440531 10030 . . 40.75 194405 19440630 10030 1212 0.25 44.5 194406 19440630 10030 1272 0.15 44.5 194406 19440731 10030 . . 43 194407 19440831 10030 . . 45.25 194408 19440930 10030 1212 0.25 42.25 194409 19440930 10030 1272 0.15 42.25 194409 19441031 10030 . . 44.25 194410 19441130 10030 . . 45 194411 19441230 10030 1212 0.25 45.75 194412 19441230 10030 1272 0.25 45.75 194412 19450131 10030 . . 44.875 194501 19450228 10030 . . 46.5 194502 19450331 10030 1212 0.25 44 194503 19450331 10030 1272 0.1 44 194503 19450430 10030 . . 46 194504 19450531 10030 . . 44.875 194505 19450630 10030 1212 0.25 44.25 194506 19450630 10030 1272 0.15 44.25 194506 19450731 10030 . . 44 194507 19450831 10030 . . 45 194508 19450929 10030 1212 0.25 -51.5 194509 19450929 10030 1272 0.15 -51.5 194509 19451031 10030 . . 52.25 194510 19451130 10030 . . 51.5 194511 19451231 10030 1212 0.25 53 194512 19451231 10030 1272 0.25 53 194512 19460131 10030 . . 56 194601 19460228 10030 . . 51 194602 19460330 10030 1212 0.25 52 194603 19460430 10030 . . 62 194604 19460531 10030 . . 61.5 194605 19460628 10030 1212 0.25 63 194606 19460628 10030 1272 0.25 63 194606 19460731 10030 . . 57 194607 ; run; %let list= 1262 1272 ; proc sql; create table temp as select permno,date,DISTCD,intnx('year',date,-2,'s') as prev_two_year format=yymmddn8.,intnx('year',date,2,'s') as next_two_year format=yymmddn8. from have where DISTCD in ( &list ) order by permno,date; quit; data group; set temp; if permno ne lag(permno) or (permno eq lag(permno) and prev_two_year gt lag(next_two_year)) then group+1; run; data date_range; set group; by group; retain start; if first.group then start=prev_two_year; if last.group then do;end=next_two_year; output;end; keep permno start end; format start end yymmddn8.; run; proc sql; create table want as select * from have except select a.* from have as a,date_range as b where a.permno=b.permno and a.date between b.start and b.end ; quit;
Ksharp
Thanks Ksharp I can understand the process and what you are doing is correct. However with this code, my want database produces the same number of observations as my have database. Surely the number would decrease by a few thousands at least?
On behalf of Theory ,It should be smaller table.
Not seeing your real data and Log , It is hard to suggest you something.
Ksharp
Hi I have attached my sample data (i am sorry this is not the whole database, too big to upload). With the log there seems to be an error with the intnx....
You will notice I have a few more variables and year is referred to as fyear.
I can't believe your INTNX can't work. Is your DATE variable SAS DATE type ? I highly skeptic it is CHARACTER.
Here is the LOG I runned the data you posted at the first . I have to leave now.
485 %let list= 1262 1272 ;
486 proc sql;
487 create table temp as
488 select permno,date,DISTCD,intnx('year',date,-2,'s') as prev_two_year
488! format=yymmddn8.,intnx('year',date,2,'s') as next_two_year format=yymmddn8.
489 from have
490 where DISTCD in ( &list )
491 order by permno,date;
NOTE: Table WORK.TEMP created, with 53 rows and 5 columns.
492 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.35 seconds
cpu time 0.04 seconds
493 data group;
494 set temp;
495 if permno ne lag(permno) or (permno eq lag(permno) and prev_two_year gt lag(next_two_year)) then
495! group+1;
496 run;
NOTE: There were 53 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.GROUP has 53 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds
497 data date_range;
498 set group;
499 by group;
500 retain start;
501 if first.group then start=prev_two_year;
502 if last.group then do;end=next_two_year; output;end;
503 keep permno start end;
504 format start end yymmddn8.;
505 run;
NOTE: There were 53 observations read from the data set WORK.GROUP.
NOTE: The data set WORK.DATE_RANGE has 6 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
506 proc sql;
507 create table want as
508 select * from have
509 except
510 select a.*
511 from have as a,date_range as b
512 where a.permno=b.permno and a.date between b.start and b.end ;
NOTE: Table WORK.WANT created, with 36 rows and 6 columns.
513 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds
Ksharp
no my dates are not in sas format they are numeric
can we tweak the code to account for this?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.