topic Re: Keeping obsv. 1mnth before a specific date in SAS Procedures

Keeping obsv. 1mnth before a specific date

spraynardz90 — Sat, 28 Jul 2012 02:38:25 GMT

-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

Re: Keeping obsv. 1mnth before a specific date

Patrick — Sat, 28 Jul 2012 12:52:40 GMT

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.

Re: Keeping obsv. 1mnth before a specific date

spraynardz90 — Sat, 28 Jul 2012 13:49:34 GMT

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

Re: Keeping obsv. 1mnth before a specific date

art297 — Sat, 28 Jul 2012 14:14:43 GMT

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?

Re: Keeping obsv. 1mnth before a specific date

spraynardz90 — Sat, 28 Jul 2012 14:35:50 GMT

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

Re: Keeping obsv. 1mnth before a specific date

art297 — Sat, 28 Jul 2012 17:32:35 GMT

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;

run;

Re: Keeping obsv. 1mnth before a specific date

Patrick — Sun, 29 Jul 2012 06:57:42 GMT

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....

Re: Keeping obsv. 1mnth before a specific date

spraynardz90 — Thu, 02 Aug 2012 15:03:49 GMT

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.

Re: Keeping obsv. 1mnth before a specific date

Ksharp — Fri, 03 Aug 2012 03:08:24 GMT

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

Re: Keeping obsv. 1mnth before a specific date

spraynardz90 — Fri, 03 Aug 2012 04:27:14 GMT

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?

Re: Keeping obsv. 1mnth before a specific date

Ksharp — Fri, 03 Aug 2012 04:40:54 GMT

On behalf of Theory ,It should be smaller table.

Not seeing your real data and Log , It is hard to suggest you something.

Ksharp

Re: Keeping obsv. 1mnth before a specific date

spraynardz90 — Fri, 03 Aug 2012 08:30:13 GMT

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.

Re: Keeping obsv. 1mnth before a specific date

Ksharp — Fri, 03 Aug 2012 09:24:10 GMT

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

Re: Keeping obsv. 1mnth before a specific date

spraynardz90 — Fri, 03 Aug 2012 09:32:19 GMT

no my dates are not in sas format they are numeric

Re: Keeping obsv. 1mnth before a specific date

spraynardz90 — Fri, 03 Aug 2012 09:33:47 GMT

can we tweak the code to account for this?

Re: Keeping obsv. 1mnth before a specific date

mkeintz — Fri, 03 Aug 2012 14:50:15 GMT

1. You have data sorted by permno and date.

2. The data is monthly (but possibly with some missing months). This is the only reason you have to compare the dates in consecutive records.

3. You've reported that the date variable (unfortunately) is not a sas date value, so you have to modify the date values before using the INTCK or INTNX function.

soapbox: why don't you keep SAS date values, in addition to your numeric values if you must. It's no harder to specify date ranges (ie.. where date between '01jan2001'd and '31dec2002'd) vs what I presuem you would do: where datemonth between 200101 and 200212), and it has the great advantage of measuring intervales in terms of days ( days=date2-date1), weeks, months, qtrs, years, etc.). soapbox off.

4. You want to look ahead one record for a particular dist code.

You can use the FIRSTOBS=-2 option in a second set statement to look ahead and check for desired DISTCD. Then you know whether to output the record-in-hand.

Using the DATA step below compares only consecutive records, so it should be much faster than the proc sql approach which needs to compare every possible record pair to decide whether to keep a particular set of records.

Also the DATA step can create two (or more) data sets in one stepm while the CREATE TABLE statement (to the best of my knowledge) can create only one table per statement. This code creates two data sets: REOCRD_PAIRS (the record preceding the 1262,1272 and the record with the 1262,1272) and RETURNS (the record with the 1262, 1272 with a new variables: return).

BTW the negative PRC value is CRSP's way of signifying that no trading took place during the month, so the value presented is the negative of the mid-point of the best bid and best ask. So you might choose to use the absolute value of PRC. I don't know off hand what DISTCD of 1262, 1272 mean, but if they are something like distributions from going bankrupt, I could see why they might have been already delisted or not traded on public stock exchanges.

data record_pairs (keep=date permno distcd divamt prc monthdate)

distcd_returns (keep=date permno distcd divamt prc monthdate return);

set have end=end_of_have; ** end_of_have=1 if current record is the last **;
by permno date; ** Establishes first.permno, last.permno, etc. dummy variables **;

/* If we are not at end of data, then read ahead on record */

if end_of_have=0 then

set have (firstobs=2 keep=distcd rename=(distcd=next_distcd));

else next_distcd=.;

** First deal with outputing observations to RECORD_PAIRS **;

** If the next record is a 1262, 1272 and record-in-hand is not end of permno, or

if current record is a 1262, 1272 and is not start of a permno then output it **;

if (next_distcd in (1262,1272) and last.permno=0) or

(distcd in(1262,1272) and first.permno=0) then output record_pairs;

** Now deal with populating the returns data set **;

lag_prc=abs(lag(prc));

lag_date=lag(date);

if distcd in (1262,1272) and first.permno=0 then do;

return=div_amt/lag_prc;

if return=. then return_grp=.;

else if return<= .05 then return_grp=1;

else if return< .1 then return_grp=2;

else return_grp=3;

** Finally if this record is 1 month after prior record, output it **;

if intck('month',input(lag_date,yymmdd8.),input(date,yymmddn8.))=1 then output returns;

end;

run;

Re: Keeping obsv. 1mnth before a specific date

Ksharp — Sun, 05 Aug 2012 04:15:58 GMT

If your date variable is NUMERIC type. You need firstly convert it into DATE type.Like:

new_date=input(put(date,best10.),yymmdd10.) ;

After that , You can run my code.

You see My log has not any ERROR message .

Ksharp