topic Re: RE: Keeping deleted data (in seperate database) in SAS Procedures

RE: Keeping deleted data (in seperate database)

spraynardz90 — Fri, 27 Jul 2012 11:53:53 GMT

Hi all

First I would like to outline what I aim to do in sas commands:

I am looking to delete the permnos with previous reference up to 2 years. To ellaborate, if the permno (company identifier) has a distcd record of 1262 or 1272 in the same year, one year prior or two years prior. The most recent record must be deleted.

Consider the following examples (see the data below):

eg1: Permno 1005 has a record of distcd 1262 for 2000. However there is also a record for permno 1005 of distcd 1272 in 1999. The 2000 observation should be deleted.

eg2: Permno 1005 has a record of distcd 1262 for 2000. However there is also a record for permno 1005 of distcd 1262 in 1998. The 2000 observation should be deleted.

eg3: Permno 1005 has a record of distcd 1272 for DECEMBER 2000. However there is also a record for permno 1005 of distcd 1262 in APRIL 2000. The DECEMBER 2000 observation should be deleted.

Also, I would like to ask whether these deletions could be saved in a separate database?

PERMNO	DISTCD	DIVAMT	YEAR	YEARMONTH	DCLRDT
10002	1272	0.08	1993	199312	19931215
10002	1272	0.25	1997	199703	19970325
10006	1272	2	1953	195307	19530702
10006	1272	1	1954	195408	19540802
10006	1272	0.1	1964	196411	19641106
10006	1272	0.2	1965	196502	19650205
10006	1272	0.15	1965	196505	19650507
10006	1272	0.15	1965	196508	19650806
10014	1272	0.15	1933	193311	19331101
10014	1272	0.15	1934	193402	19340201
10014	1272	0.15	1934	193405	19340501
10014	1272	0.15	1934	193408	19340801
10014	1272	0.15	1934	193411	19341101
10014	1272	0.15	1935	193502	19350201
10014	1272	0.15	1935	193505	19350501
10014	1272	0.15	1935	193508	19350801
10014	1272	0.15	1935	193511	19351101
10014	1272	0.15	1936	193602	19360201
10014	1272	0.15	1936	193605	19360501
10014	1272	0.15	1936	193608	19360801
10014	1272	0.15	1936	193611	19361102
10014	1272	0.15	1937	193702	19370201
10014	1272	0.15	1937	193705	19370501
10014	1272	0.15	1937	193708	19370802
10014	1272	0.3	1937	193711	19371101
10014	1272	0.15	1938	193802	19380201
10014	1272	0.15	1938	193805	19380502
10014	1272	0.15	1938	193808	19380801
10014	1272	0.15	1938	193811	19381101
10016	1272	0.2	1988	198811	19881121
10022	1272	0.25	1927	192712	19271209
10022	1272	0.25	1928	192803	19280312
10022	1272	0.25	1928	192806	19280611
10022	1272	0.25	1928	192809	19280910
10022	1272	0.25	1928	192812	19281210
10022	1272	0.25	1929	192903	19290308
10022	1272	0.25	1929	192906	19290610
10022	1272	0.25	1929	192909	19290910
10022	1272	0.25	1935	193503	19350330
10022	1272	0.25	1936	193612	19361219
10022	1272	0.25	1942	194211	19421116
10022	1272	0.25	1947	194701	19470115
10030	1272	0.05	1935	193503	19350330
10030	1272	0.25	1935	193512	19351231
10030	1272	1	1936	193612	19361221
10030	1272	0.25	1939	193912	19391221
10030	1272	0.6	1940	194012	19401228
10030	1272	0.1	1941	194106	19410630
10030	1272	0.1	1941	194109	19410930
10030	1272	0.4	1941	194112	19411227
10030	1272	0.1	1942	194206	19420630
10030	1272	0.1	1942	194209	19420930
10030	1272	0.5	1942	194212	19421230
10030	1272	0.15	1943	194306	19430630
10030	1272	0.15	1943	194309	19430930
10030	1272	0.5	1943	194312	19431228
10030	1272	0.1	1944	194403	19440331
10030	1272	0.15	1944	194406	19440630
10030	1272	0.15	1944	194409	19440930
10030	1272	0.25	1944	194412	19441228
10030	1272	0.1	1945	194503	19450331
10030	1272	0.15	1945	194506	19450630
10030	1272	0.15	1945	194509	19450929
10030	1272	0.25	1945	194512	19451228
10030	1272	0.25	1946	194606	19460629
10030	1262	1	1950	195009	19500929
10030	1272	0.5	1951	195112	19511226
10030	1272	0.5	1956	195612	19561228
10030	1272	0.5	1957	195712	19571230
10057	1272	0.25	1936	193611	19361120
10057	1272	0.5	1946	194611	19461120
10057	1272	0.5	1947	194711	19471125
10057	1272	0.5	1950	195011	19501121
10057	1272	0.5	1951	195108	19510823
10057	1272	0.5	1951	195111	19511121
10057	1272	0.5	1952	195208	19520821
10057	1272	0.5	1952	195211	19521120
10057	1272	0.5	1953	195305	19530521
10057	1272	0.5	1953	195311	19531120
10057	1272	0.5	1953	195311	19531120
10057	1272	0.5	1954	195405	19540520
10057	1272	0.5	1954	195411	19541123
10057	1262	1	1954	195411	19541123
10057	1272	0.5	1955	195505	19550524
10057	1272	0.5	1955	195508	19550823
10057	1272	1	1955	195511	19551123
10057	1272	0.5	1956	195605	19560524
10057	1272	0.5	1956	195608	19560823
10057	1272	1	1956	195611	19561124
10057	1272	1	1956	195611	19561124
10057	1272	0.5	1957	195705	19570523
10057	1272	0.5	1957	195708	19570822
10057	1272	0.5	1957	195711	19571122
10057	1272	1	1957	195711	19571122
10057	1262	0.5	1959	195911	19591120
10057	1272	0.5	1960	196011	19601121
10057	1262	0.5	1961	196111	19611117
10057	1262	1.25	1962	196211	19621123
10057	1262	1.25	1963	196311	19631126
10057	1262	1.75	1964	196411	19641124
10057	1272	0.5	1965	196507	19650722
10057	1262	1	1965	196511	19651123
10057	1272	0.5	1966	196607	19660728
10057	1262	0.8	1966	196611	19661121
10057	1262	0.5	1967	196711	19671121
10057	1262	0.15	1968	196811	19681127
10058	1272	0.1	1973	197301	19730102
10065	1262	0.77	1950	195009	19500922
10065	1262	0.84	1951	195109	19510928
10065	1262	0.7	1952	195209	19520926
10065	1262	0.67	1953	195309	19530925
10065	1262	0.66	1954	195409	19540925
10065	1262	0.4	1955	195510	19551018
10065	1262	0.47	1956	195609	19560927
10065	1262	0.47	1957	195709	19570927
10065	1262	0.22	1970	197001	19700107
10065	1262	0.19	1976	197601	19760108
10065	1262	0.1442	1978	197801	19780112
10065	1262	0.23664	1979	197901	19790111
10065	1262	0.26743	1980	198001	19800110
10065	1262	0.35255	1981	198101	19810108
10065	1262	0.37296	1982	198201	19820114
10065	1262	0.37016	1983	198301	19830113
10065	1262	0.2997	1984	198401	19840112
10065	1262	0.24548	1985	198501	19850110
10065	1262	0.24372	1986	198601	19860109
10065	1262	0.23	1987	198701	19870108
10065	1262	0.31	1987	198711	19871112
10065	1262	0.14	1998	199811	19981119
10065	1262	0.03	2000	200010	20001009
10065	1272	0.01	2007	200701	20070111
10081	1272	0.175	1926	192603	19260318
10081	1272	0.175	1926	192606	19260618
10081	1272	0.175	1926	192609	19260928
10081	1272	0.075	1926	192612	19261217
10081	1272	0.05	1927	192703	19270325
10084	1272	0.25	1989	198907	19890712
10085	1272	0.25	2005	200502	20050209
10102	1272	1	1926	192609	19260930
10102	1272	2	1927	192709	19270930
10102	1272	1	1928	192809	19280928
10102	1272	1.5	1929	192909	19290930
10102	1272	1.5	1930	193009	19300930
10102	1272	1.5	1931	193109	19310930
10102	1272	0.75	1933	193310	19331016
10102	1272	1.5	1934	193410	19341015
10102	1272	1	1935	193507	19350715
10102	1272	1.5	1935	193510	19351015
10102	1272	0.5	1936	193607	19360715
10102	1272	1	1936	193610	19361015
10102	1272	0.25	1937	193701	19370115
10102	1272	0.25	1937	193704	19370415
10102	1272	0.75	1937	193707	19370715
10102	1272	0.75	1937	193710	19371015
10102	1272	0.25	1938	193801	19380115
10102	1272	0.25	1938	193810	19381015
10102	1272	0.5	1939	193910	19391016
10102	1272	0.25	1940	194004	19400415
10102	1272	0.25	1940	194007	19400715
10102	1272	0.25	1940	194010	19401015
10102	1272	0.25	1941	194101	19410115
10102	1272	0.25	1941	194104	19410415
10102	1272	0.25	1941	194107	19410715
10102	1272	0.25	1941	194110	19411015
10102	1272	0.25	1942	194201	19420115
10102	1272	0.25	1942	194204	19420415
10102	1272	0.25	1942	194207	19420715
10102	1272	0.25	1942	194210	19421015
10102	1272	0.25	1943	194301	19430115
10102	1272	0.25	1943	194304	19430415
10102	1272	0.25	1943	194307	19430715
10102	1272	0.25	1943	194310	19431015
10102	1272	0.25	1944	194401	19440115
10102	1272	0.25	1944	194404	19440415
10102	1272	0.25	1944	194407	19440710
10102	1272	0.25	1944	194410	19441010
10102	1272	0.25	1945	194501	19450110
10102	1272	0.25	1945	194504	19450416
10102	1272	0.25	1945	194507	19450716
10102	1272	0.25	1945	194510	19451015
10102	1272	0.25	1946	194601	19460115
10102	1272	0.25	1946	194604	19460425
10102	1272	0.25	1946	194607	19460725
10107	1272	3	2004	200411	20041109
10119	1272	0.0055	2007	200709	20070907
10120	1272	0.65	1986	198611	19861119
10120	1272	0.5	1987	198711	19871111
10120	1272	0.45	1988	198811	19881109
10120	1272	4.8	1989	198911	19891108
10120	1272	0.6	1990	199011	19901108
10122	1272	0.12	1993	199312	19931201
10122	1272	0.08	1994	199411	19941130
10122	1272	0.12	1995	199511	19951129
10122	1272	0.15	1996	199611	19961127
10122	1272	0.07	1999	199912	19991201
10122	1272	0.04	2000	200011	20001129
10127	1272	0.2	1998	199810	19981015
10127	1272	0.1	2000	200003	20000309
10145	1272	1.5	1937	193711	19371101
10145	1272	3	1939	193912	19391220
10145	1272	2	1940	194012	19401220
10145	1272	2	1941	194112	19411220
10145	1272	1	1942	194212	19421219
10145	1272	2	1946	194612	19461220
10145	1272	3	1947	194712	19471220
10145	1272	3	1948	194812	19481220
10145	1272	4	1949	194912	19491220
10145	1272	1	1950	195012	19501220
10145	1272	1	1951	195112	19511220
10145	1272	0.6	1952	195212	19521212
10145	1272	0.6	1953	195312	19531210
10145	1272	0.15	1954	195412	19541210
10146	1272	0.05	1974	197412	19741218
10146	1272	0.1	1977	197712	19771222
10146	1272	0.1	1978	197812	19781220
10146	1272	0.15	1979	197912	19791219
10153	1272	0.5	1936	193612	19361224
10153	1272	0.25	1939	193912	19391222
10153	1272	0.15	1945	194512	19451224
10153	1272	0.4	1949	194912	19491223
10153	1272	1	1950	195012	19501222
10153	1272	0.25	1959	195912	19591223
10161	1272	0.25	1937	193709	19370901
10161	1272	0.25	1944	194412	19441201
10161	1272	1	1947	194712	19471201
10161	1272	1	1948	194811	19481130
10161	1262	1	1949	194912	19491201
10161	1262	1	1950	195012	19501201
10161	1272	1	1951	195112	19511201
10161	1262	0.5	1952	195212	19521201
10161	1272	0.5	1953	195312	19531201
10161	1272	0.65	1954	195412	19541201
10161	1262	1	1955	195512	19551217
10161	1272	0.6	1956	195612	19561201
10188	1272	0.5	1948	194809	19480902
10188	1272	0.25	1949	194904	19490415
10188	1262	0.5	1962	196211	19621127
10188	1262	0.5	1963	196311	19631120
10196	1272	0.15	1954	195412	19541210
10196	1272	0.15	1955	195512	19551209
10207	1272	0.1715	1991	199108	19910823
10207	1272	0.53	1997	199711	19971126
10209	1272	0.25	1941	194112	19411227
10209	1272	0.5	1942	194209	19420930
10209	1272	0.8	1943	194309	19430930
10209	1272	0.8	1944	194409	19440929
10209	1272	0.8	1945	194509	19450927
10209	1272	1	1946	194609	19460926
10209	1272	1.5	1947	194709	19470925
10209	1272	1.5	1948	194809	19480928
10209	1272	1.5	1949	194909	19490929
10209	1272	1.5	1950	195009	19500928
10209	1272	1.5	1951	195109	19510927
10209	1272	1.5	1952	195209	19520926
10209	1272	1.5	1953	195309	19530925
10209	1272	1.5	1954	195409	19540924
10209	1272	1.5	1955	195509	19550923
10209	1272	1.5	1956	195609	19560921
10209	1272	1.5	1957	195709	19570920
10209	1272	1.5	1958	195809	19580919
10214	1272	0.5	1997	199704	19970408
10217	1272	1	1926	192610	19261001
10217	1272	1	1927	192712	19271212
10217	1272	1	1928	192812	19281207
10217	1272	1	1929	192912	19291210
10217	1272	1	1930	193010	19301001
10217	1272	1	1931	193112	19311210
10217	1272	0.15	1936	193612	19361224
10217	1272	0.2	1945	194501	19450102
10217	1272	0.25	1946	194601	19460102
10217	1272	0.7	1947	194701	19470102
10217	1272	0.4	1948	194801	19480102
10217	1272	0.6	1949	194901	19490103
10217	1262	0.4	1949	194910	19491001
10217	1272	0.1	1956	195601	19560103
10217	1272	0.15	1957	195701	19570102
10225	1272	2	1929	192911	19291108
10225	1272	4	1930	193008	19300808
10225	1272	1	1931	193102	19310210
10225	1272	1	1932	193202	19320210
10225	1272	0.25	1943	194303	19430301
10225	1272	0.25	1943	194312	19431201
10225	1272	0.25	1945	194503	19450301
10225	1272	0.25	1946	194603	19460301
10225	1272	0.5	1947	194703	19470301
10225	1272	0.75	1948	194803	19480301
10225	1272	1	1949	194903	19490301
10225	1272	1	1950	195003	19500301
10225	1272	1	1951	195103	19510302
10225	1272	1	1952	195203	19520303
10225	1272	1	1953	195303	19530302
10225	1272	1	1954	195403	19540302
10225	1272	1	1955	195503	19550301
10225	1272	1	1956	195603	19560301
10225	1272	1	1957	195703	19570301
10225	1272	1	1958	195803	19580301
10225	1272	1	1959	195903	19590302
10225	1272	1	1960	196003	19600301
10232	1272	0.2	1992	199211	19921123
10233	1272	0.25	1946	194612	19461227
10233	1272	0.2	1955	195512	19551220
10233	1272	0.3	1956	195612	19561220
10234	1272	0.2	1979	197906	19790604
10239	1272	0.125	1992	199211	19921110
10239	1272	0.1	1994	199411	19941102
10239	1272	0.25	2003	200311	20031104
10239	1272	0.4	2004	200402	20040203
10239	1272	0.3	2004	200405	20040504
10239	1272	0.05	2004	200408	20040803
10239	1272	0.9	2004	200411	20041102
10239	1272	0.15	2005	200502	20050201
10239	1272	0.25	2005	200508	20050802
10239	1272	0.15	2005	200511	20051101
10239	1272	0.25	2006	200602	20060215
10239	1272	1.25	2006	200605	20060502
10239	1272	0.2	2006	200611	20061107
10239	1272	0.2	2007	200702	20070206
10239	1272	0.35	2007	200708	20070807
10239	1272	0.1	2007	200711	20071107
10239	1272	0.25	2010	201002	20100210
10241	1272	3	1926	192601	19260129
10241	1272	1	1928	192811	19281115
10241	1272	1	1929	192911	19291115
10241	1272	1	1930	193010	19301031
10241	1272	1	1931	193111	19311102
10241	1272	1	1935	193502	19350215
10241	1272	1	1936	193602	19360215
10241	1272	1	1936	193611	19361116
10241	1272	1	1948	194811	19481115
10241	1272	1	1949	194911	19491115
10241	1272	2	1950	195011	19501115
10241	1272	1	1951	195111	19511115
10241	1272	0.15	1954	195411	19541115
10241	1272	0.2	1974	197412	19741210
10252	1272	0.09	1986	198611	19861113
10252	1272	0.0667	1987	198711	19871119
10266	1272	0.06	1987	198701	19870116
10268	1272	2	1926	192601	19260108
10268	1272	2	1927	192701	19270107
10268	1272	0.5	1928	192801	19280106
10268	1272	1	1929	192901	19290107
10268	1272	1	1930	193001	19300107
10268	1272	0.1	1950	195001	19500104
10268	1272	0.25	1956	195601	19560104
10268	1272	0.25	1958	195801	19580103
10268	1272	0.1	1959	195910	19591002
10268	1272	0.1	1960	196001	19600105
10268	1272	0.1	1960	196004	19600407
10268	1272	0.05	1964	196411	19641124
10276	1272	1	1948	194801	19480102
10276	1272	0.3	1951	195104	19510402
10276	1272	1.45	1952	195203	19520331
10276	1272	0.3	1953	195303	19530331
10276	1272	0.25	1960	196003	19600331
10284	1272	2	1938	193801	19380103
10290	1272	0.15	1987	198702	19870225
10290	1272	1.44	2010	201011	20101101
10292	1272	0.25	1929	192912	19291212
10292	1272	0.25	1930	193003	19300312
10292	1272	0.25	1930	193006	19300612
10292	1272	0.25	1930	193009	19300912
10292	1272	0.25	1930	193012	19301212
10292	1272	0.25	1931	193103	19310312
10292	1272	0.25	1931	193106	19310612
10292	1272	0.25	1931	193109	19310911
10292	1272	0.25	1931	193112	19311211
10292	1272	0.25	1932	193203	19320311
10292	1272	0.25	1932	193206	19320610
10292	1272	0.25	1932	193209	19320912
10292	1272	0.25	1932	193212	19321212
10292	1272	0.25	1933	193304	19330401
10292	1272	0.25	1933	193306	19330612
10292	1272	0.25	1933	193310	19331002
10292	1272	0.25	1934	193401	19340102
10292	1272	0.5	1935	193501	19350102
10292	1272	0.5	1935	193507	19350701
10292	1272	0.25	1936	193601	19360102
10292	1272	0.25	1936	193604	19360401
10292	1272	1.5	1936	193612	19361215
10292	1272	1	1937	193706	19370615
10292	1272	2	1937	193712	19371215
10292	1272	0.5	1938	193806	19380615
10292	1272	1	1938	193812	19381215
10292	1272	1	1939	193906	19390615
10292	1272	1.25	1939	193909	19390915
10292	1272	1.25	1940	194006	19400615
10292	1272	1.25	1940	194012	19401216
10292	1272	1	1941	194106	19410616
10292	1272	1	1941	194112	19411215
10292	1272	0.5	1942	194206	19420615
10292	1272	0.5	1942	194212	19421215
10292	1272	0.5	1943	194312	19431215
10292	1272	0.5	1944	194406	19440615
10292	1272	0.5	1944	194412	19441215
10292	1272	0.5	1945	194506	19450615
10292	1272	0.5	1945	194512	19451215
10292	1272	0.5	1946	194606	19460615
10292	1272	0.5	1946	194612	19461214
10292	1272	0.5	1947	194706	19470614
10292	1272	0.5	1947	194712	19471215
10292	1272	0.25	1948	194806	19480615
10292	1272	0.25	1948	194812	19481210
10292	1272	0.25	1949	194906	19490610
10292	1272	0.25	1949	194912	19491210
10292	1272	0.25	1950	195006	19500610
10292	1272	0.25	1950	195012	19501209
10292	1272	0.25	1951	195106	19510609
10292	1272	0.25	1951	195112	19511210
10292	1272	0.25	1952	195206	19520610
10292	1272	0.25	1952	195212	19521210
10292	1272	0.25	1953	195306	19530610
10292	1272	0.25	1953	195312	19531210
10292	1272	0.5	1954	195409	19540910
10292	1272	0.25	1955	195506	19550610
10292	1272	0.25	1955	195512	19551210
10292	1272	0.25	1956	195606	19560611
10292	1272	0.25	1956	195612	19561210
10292	1272	0.5	1957	195712	19571210
10292	1272	0.75	1958	195812	19581210
10292	1272	0.4	1959	195912	19591210
10292	1272	0.4	1960	196012	19601209
10292	1272	0.5	1961	196112	19611211
10294	1272	0.095	2010	201011	20101109
10297	1272	0.03	1987	198711	19871112
10297	1272	0.05	1988	198812	19881208
10297	1272	0.05	1989	198911	19891117
10297	1272	0.05	1993	199311	19931110
10308	1272	0.21	1986	198612	19861209
10308	1272	0.21	1987	198712	19871208
10308	1272	0.05	1995	199511	19951114
10313	1262	0.49	1951	195109	19510926
10313	1262	0.4	1952	195209	19520923
10313	1262	0.37	1953	195309	19530922
10313	1262	0.36	1954	195409	19540922
10313	1262	0.21	1955	195510	19551018
10313	1262	0.26	1956	195609	19560924
10321	1272	1	1926	192607	19260720
10321	1272	0.5	1926	192610	19261020
10321	1272	0.5	1927	192707	19270720
10321	1272	0.5	1927	192710	19271020
10321	1272	0.5	1928	192801	19280120
10321	1272	0.5	1928	192804	19280420
10321	1272	0.5	1928	192807	19280720
10321	1272	0.5	1928	192810	19281019
10321	1272	0.75	1949	194912	19491229
10321	1272	0.5	1950	195009	19500922
10321	1262	0.5	1950	195012	19501228
10321	1272	0.6	1959	195912	19591230
10321	1262	0.2	1962	196211	19621115
10321	1272	0.15	1963	196311	19631118
10340	1272	0.2	1986	198611	19861112
10341	1272	0.05	1998	199803	19980326
10341	1272	0.15	1999	199903	19990325
10364	1272	2.25	1936	193611	19361130

Re: RE: Keeping deleted data (in seperate database)

art297 — Fri, 27 Jul 2012 12:47:29 GMT

Do you only want to delete one record per permno? e.g., for the following case, which record(s) should be deleted?

10006	1272	2	1953	195307	19530702
10006	1272	1	1954	195408	19540802
10006	1272	0.1	1964	196411	19641106
10006	1272	0.2	1965	196502	19650205
10006	1272	0.15	1965	196505	19650507
10006	1272	0.15	1965	196508	19650806

Re: RE: Keeping deleted data (in seperate database)

Quentin — Fri, 27 Jul 2012 12:59:35 GMT

Hi,

Not sure I understand your requirements completely, but something like this might help:

data want
     dropped
  ;
  set have;
  by PermNo Year;
  retain _Year _dropflag; 
  if first.PERMNO then do;
    _year=.;
    _dropflag=.;
  end;
  if distcd IN (1262,1272) then do ;
    if not missing(_year) then do ;
      _dropflag=((year-_year) le 2) ;
    end ;
    _year=year;
  end;
  if last.PermNo and _dropflag then output dropped;
  else output want;
run;

Re: RE: Keeping deleted data (in seperate database)

Quentin — Fri, 27 Jul 2012 13:03:18 GMT

Sorry, there was at least one obvious mistake in my sugggestion. Something like this maybe:

data want
     dropped
  ;
  set have;
  by PermNo Year;
  retain _Year _dropflag; 
  if first.PERMNO then do;
    _year=.;
    _dropflag=.;
  end;
  if distcd IN (1262,1272) then do ;
    if (not missing(_year)) and (not _dropflag) then do ;   *added not _dropflag to avoid it being reset to 0 after it has been set to 1;
      _dropflag=((year-_year)le 2) ;
    end ;
    _year=year;
  end;
  if last.PermNo and _dropflag then output dropped;
  else output want;
run;

Re: RE: Keeping deleted data (in seperate database)

spraynardz90 — Fri, 27 Jul 2012 13:19:30 GMT

Sorry if my blurb above is confusing. Basically I want firms that don't have the distcd regularly. I want the firms that have distcd 1272 or 1262 rarely, not commonly. Therefore I imposed the 2 year restriction. I thought if I could find the firms that only have 1262 or 1272 once every two years or longer this is not common and would be very helpful.

If first, we could write a command to automatically delete any permno with multiple SDD paid in a single year this would be very helpful.

Then second, impose the original restriction about two years. In which case, Arthur 195307 would be the only observation included.

Re: RE: Keeping deleted data (in seperate database)

spraynardz90 — Fri, 27 Jul 2012 13:28:33 GMT

Thanks Quentin, to clarify looking to:

-delete any observations with for a permno with multiple 1262 and 1272 observed in a single year. (see Arthur's example)

-ensure that every permno in the database has a separation of 2 years or longer for each observation.

EG: Permno 1005 dist 1262 in 1999 and permno 1005 dist 1272 in 2000 = DELETED (the 2000 observation)

EG: Permno 1005 dist 1272 in 1999 and permno 1005 dist 1272 in 2002 =DESIRED. No deletions

Does your command satisfy the above conditions?

Re: RE: Keeping deleted data (in seperate database)

art297 — Fri, 27 Jul 2012 13:32:32 GMT

Why wouldn't 196411 also be included?

Re: RE: Keeping deleted data (in seperate database)

art297 — Fri, 27 Jul 2012 13:57:37 GMT

You didn't answer my question. If 196411 should have been kept, then you might be able to get what you want with something like:

data want1 leftover;

set have;

if distcd IN (1262,1272) then output leftover;

else output want1;

run;

data want2 deleted;

set leftover;

by permno;

last_year=lag(year);

if first.permno then output want2;

else do;

if year-last_year le 2 then output deleted;

else output want2;

end;

run;

data want;

set want1 want2;

run;

proc sort data=want;

by permno year;

run;

Re: RE: Keeping deleted data (in seperate database)

Quentin — Fri, 27 Jul 2012 14:20:57 GMT

Well, I'm still confused as to what the rules are for what you want. Maybe post an example with ~20 records for ~3 or 4 permnos, and show what you would want for output.

In any case, both Art and I took similar appraoches, using some sort of look-back (mine with by-group processing, his with lagging). I think playing with either might get you where you want. And of course there is likely SQL solution....

--Q.

Re: RE: Keeping deleted data (in seperate database)

tish — Fri, 27 Jul 2012 15:02:00 GMT

This works, given my understanding of what you need. Note that I read yearmonth in as a SAS date value.

data raw;

input

permno

distcd

divamt : 7.5

year

yearmonth : yymmn6.

dclrdt : yymmdd8.;

format yearmonth monyy7. dclrdt date9.;

cards;

;

run;

proc sort data=raw;

by permno dclrdt;

run;

data desired discarded;

set raw;

by permno;

retain index_year;

if first.permno then

do;

index_year = year(yearmonth);

output desired;

end;

else if

intck("year", index_year, yearmonth) > 2 then

do;

index_year = year(yearmonth);

output desired;

end;

else

output discarded;

run;

Re: RE: Keeping deleted data (in seperate database)

spraynardz90 — Fri, 27 Jul 2012 15:15:03 GMT

I seem to have overlooked 196411, yes it should be included.

Re: RE: Keeping deleted data (in seperate database)

tish — Fri, 27 Jul 2012 16:31:01 GMT

Did you try the code I gave you?

Re: RE: Keeping deleted data (in seperate database)

spraynardz90 — Fri, 27 Jul 2012 16:32:47 GMT

Hi Arthur I followed your method and seem to have inconsistent results.

I would like one database with all deletions.

And another database with firms with a 2 year or greater hiatus in the dist 1262 or 1272. So only the observations with the permnos that have 1262 or 1272 with a separation of 2 years or greater.

I realise some firms may have distcd 1262 and 1272 at regular intervals throughout the years and the question was raised whether to delete the regular observations to manipulate the 2 year hiatus. Preferably do not do this. But if there is still some confusion you can.

See the attachment with what I am getting. Please let me know if you would like a sample of my original data.

Re: RE: Keeping deleted data (in seperate database)

spraynardz90 — Fri, 27 Jul 2012 16:33:19 GMT

Giving it a go now tish

Re: RE: Keeping deleted data (in seperate database)

spraynardz90 — Fri, 27 Jul 2012 16:38:49 GMT

Hi Tish

I have my dates in numeric not sas date. This is intentional, required for some of my future analysis. Is it possible to tweak the code to account for numeric dates.

Re: RE: Keeping deleted data (in seperate database)

tish — Fri, 27 Jul 2012 17:22:27 GMT

Okay, how about the following? You can drop index_year and test_yearmonth as you see fit.

data desired discarded;

set raw;

by permno;

retain index_year;

test_yearmonth = input(yearmonth, yymmn6.);

if first.permno or

intck("year", index_year, test_yearmonth) > 2 then

do;

index_year = year(test_yearmonth);

output desired;

end;

else

output discarded;

run;

Re: RE: Keeping deleted data (in seperate database)

art297 — Fri, 27 Jul 2012 17:52:04 GMT

I still probably don't understand what you are trying to do and your examples don't seem to match the example dataset that you provided.

Like Tish, I changed my code to use the actual date, but didn't keep the calculated SAS date,

data want1 leftover;
set have;
if distcd IN (1262,1272) then output leftover;
else output want1;
run;

data want2 deleted;
set leftover;
by permno;
last_DCLRDT=lag(DCLRDT);
if first.permno then output want2;
else do;
    if intck('year',input(put(last_DCLRDT,8.),yymmdd8.),input(put(DCLRDT,8.),yymmdd8.),'c')
     le 2 then output deleted;
    else output want2;
end;
drop last_:;
run;

data want;
set want1 want2;
run;

proc delete data=want1;
run;

proc delete data=want2;
run;

proc delete data=leftover;
run;

proc sort data=want;
by permno year;
run;

Re: RE: Keeping deleted data (in seperate database)

bream_bn — Fri, 27 Jul 2012 18:58:44 GMT

I came up with the below

data finaldata;

set testdata;

by permno distcd yearmonth ;

retain last_yearmo;

proc sort data=testdata;

by permno distcd yearmonth ;

run;

if not first.permno or not first.distcd then do;

val = yearmonth - last_yearmo;

if val <= 200 then delete;

end;

last_yearmo=yearmonth;

run;

This though would delete 196600 from (196400 196600 196800) and not 196800

Re: RE: Keeping deleted data (in seperate database)

ncross — Sun, 19 Aug 2012 06:47:12 GMT

Hi Tish and Arthur I try tweaking the codes to 3 year or 5 year hiatus and I am receiving the same output. Where I see the "2" i replace the digit is there something I am doing wrong?

Re: RE: Keeping deleted data (in seperate database)

art297 — Sun, 19 Aug 2012 13:12:48 GMT

I'd suggest starting a new thread, including a small example dataset, and explain what you are trying to do and the result (given the example) that you want to achieve.