topic HELP: 3 precise and challenging commands in SAS Procedures

HELP: 3 precise and challenging commands

spraynardz90 — Tue, 26 Jun 2012 06:41:39 GMT

Hi I am looking to perform the following 3 functions.

-Create a new column called ‘Initiation’. The first time a permn number appears a value of 1 will correspond in the initiation column.

- Create a new column called ‘Omission’. If there has been a 5 year or greater hiatus in the appearance of the permn number a value of 1 will correspond in the omission column for the last observation documented for that permn number.

- Create a new column called ‘Change’. If the div amt changes, either increase or decrease a value of 1 will correspond in the change column for the year the dividend amount was changed

. -Something a little more complicated….. If the firm reappears after a period of 10 years or more. Then a value 1 will correspond in the initiation column for the 1st year the permn number reappears.

Sorry this is quite a mouthful. I am having difficulties with writing the commands for such precise functions. Help would be greatly appreciated. See data below.

PERMNO	DIVAMT	YEAR
10001	0.16	2007
10001	0.16	2007
10001	0.15	2007
10001	0.14	2006
10001	0.12	2006
10001	0.1	2006
10001	0.08	2006
10001	0.05	2006
10001	0.04	2005
10001	0.135	2003
10001	0.135	2002
10001	0.135	2002
10001	0.135	2002
10001	0.13	2002
10001	0.13	2001
10001	0.13	2001
10001	0.13	2001
10001	0.125	2001
10001	0.125	2000
10001	0.125	2000
10001	0.125	2000
10001	0.12	2000
10001	0.12	1999
10001	0.12	1999
10001	0.12	1999
10001	0.115	1999
10001	0.115	1998
10001	0.115	1998
10001	0.115	1998
10001	0.11	1998
10001	0.11	1997
10001	0.11	1997
10001	0.11	1997
10001	0.105	1997
10001	0.105	1996
10001	0.105	1996
10001	0.105	1996
10001	0.1	1996
10001	0.1	1995
10001	0.1	1995
10001	0.1	1995
10001	0.095	1995
10001	0.095	1994
10001	0.095	1994
10001	0.19	1994
10001	0.175	1994
10001	0.175	1993
10001	0.175	1993
10001	0.175	1993
10001	0.1575	1993
10001	0.1575	1992
10001	0.1575	1992
10001	0.1575	1992
10001	0.15	1992
10001	0.15	1991
10001	0.15	1991
10001	0.15	1991
10001	0.1375	1991
10001	0.1375	1990
10001	0.1375	1990
10001	0.1375	1990
10001	0.125	1990
10001	0.125	1989
10001	0.125	1989
10001	0.12	1989
10001	0.12	1989
10001	0.11	1988
10001	0.11	1988
10001	0.11	1988
10001	0.11	1988
10001	0.105	1987
10001	0.105	1987
10001	0.105	1987
10001	0.105	1987
10001	0.105	1986
10001	0.105	1986
10001	0.105	1986
10001	0.095	1986
10002	0.01	2009
10002	0.025	2009
10002	0.13	2008
10002	0.13	2008
10002	0.13	2008
10002	0.13	2008
10002	0.13	2007
10002	0.13	2007
10002	0.13	2007
10002	0.13	2007
10002	0.13	2006
10002	0.13	2006
10002	0.13	2006
10002	0.13	2006
10002	0.13	2005
10002	0.13	2005
10002	0.13	2005
10002	0.13	2005
10002	0.13	2004
10002	0.13	2004
10002	0.13	2004
10002	0.13	2004
10002	0.13	2003
10002	0.13	2003
10002	0.13	2003
10002	0.13	2003
10002	0.12	2002
10002	0.12	2002
10002	0.12	2002
10002	0.12	2002
10002	0.11	2001
10002	0.11	2001
10002	0.11	2001
10002	0.11	2001
10002	0.1	2000
10002	0.1	2000
10002	0.1	2000
10002	0.1	2000
10002	0.09	1999
10002	0.09	1999
10002	0.09	1999
10002	0.085	1999
10002	0.085	1998
10002	0.085	1998
10002	0.1275	1998
10002	0.11	1997
10002	0.11	1997
10002	0.1	1997
10002	0.1	1997
10002	0.1	1996
10002	0.1	1996
10002	0.1	1996
10002	0.1	1996
10002	0.08	1995
10002	0.08	1995
10002	0.08	1995
10002	0.08	1995
10002	0.07	1994
10002	0.07	1994
10002	0.06	1994
10002	0.06	1994
10002	0.05	1993
10002	0.11	1998
10003	0.275	1989
10003	0.25	1989
10003	0.25	1989
10003	0.25	1988
10003	0.25	1988
10003	0.25	1988
10003	0.25	1988
10003	0.25	1987
10003	0.1	1987
10006	0.35	1984
10006	0.35	1983
10006	0.35	1983
10006	0.35	1983
10006	0.69	1983
10006	0.69	1982
10006	0.69	1982
10006	0.69	1982
10006	0.69	1982
10006	0.69	1981
10006	0.69	1981
10006	0.69	1981
10006	0.625	1981
10006	0.625	1980
10006	0.625	1980
10006	0.625	1980
10006	0.56	1980
10006	0.56	1979
10006	0.56	1979
10006	0.56	1979
10006	0.525	1979
10006	0.525	1978
10006	0.525	1978
10006	0.525	1978
10006	0.5	1978
10006	0.5	1977
10006	0.5	1977
10006	0.5	1977
10006	0.45	1977
10006	0.45	1976
10006	0.45	1976
10006	0.65	1976
10006	0.65	1976
10006	0.65	1975
10006	0.65	1975
10006	0.65	1975
10006	0.65	1975
10006	0.65	1974
10006	0.65	1974
10006	0.6	1974
10006	0.6	1974
10006	0.6	1973
10006	0.6	1973
10006	0.6	1973
10006	0.6	1973
10006	0.6	1972
10006	0.6	1972
10006	0.6	1972
10006	0.6	1972
10006	0.6	1971
10006	0.6	1971
10006	0.6	1971
10006	0.6	1971
10006	0.6	1970
10006	0.6	1970
10006	0.6	1970
10006	0.6	1970
10006	0.6	1969
10006	0.6	1969
10006	0.6	1969
10006	0.6	1969
10006	0.6	1968
10006	0.55	1968
10006	0.55	1968
10006	0.55	1968
10006	0.55	1967
10006	0.55	1967
10006	0.55	1967
10006	0.55	1967
10006	0.55	1966
10006	0.55	1966
10006	0.55	1966
10006	0.45	1966
10006	0.45	1965
10006	0.75	1965
10006	0.75	1965
10006	0.625	1965
10006	0.625	1964
10006	0.625	1964
10006	0.5	1964
10006	0.4	1964
10006	0.4	1963
10006	0.7	1963
10006	0.7	1963
10006	0.625	1963
10006	0.625	1962
10006	0.625	1962
10006	0.625	1962
10006	0.625	1961
10006	0.625	1961
10006	0.625	1961
10006	0.625	1961
10006	0.625	1960
10006	0.625	1960
10006	0.625	1960
10006	0.625	1960
10006	0.625	1959
10006	0.625	1959
10006	0.625	1959
10006	0.625	1959
10006	0.625	1958
10006	0.625	1958
10006	0.625	1958
10006	0.625	1958
10006	1	1957
10006	1	1957
10006	1	1957
10006	1	1957
10006	1	1957
10006	1	1956
10006	1	1956
10006	1	1956
10006	1	1955
10006	0.25	1931
10006	0.75	1931
10006	1.5	1930
10006	1.5	1930
10006	1.5	1930
10006	1.5	1930
10006	1.5	1929
10006	1.5	1929
10006	1.5	1929
10006	1.5	1929
10006	1.5	1928
10006	1.5	1928
10006	1.5	1928
10006	1.5	1928
10006	1.5	1927
10006	1.5	1927
10006	1.5	1927
10006	1.5	1927
10006	1.5	1926
10006	1.5	1926
10006	1.5	1926
10006	1.5	1926
10009	0.12	2000
10009	0.12	2000
10009	0.12	1999
10009	0.12	1999
10009	0.1	1999
10009	0.1	1999
10009	0.1	1998
10009	0.1	1998
10009	0.1	1998
10009	0.1	1998
10009	0.1	1997
10009	0.1	1997
10009	0.08	1997
10009	0.08	1997
10009	0.08	1996
10009	0.08	1996
10009	0.08	1996
10009	0.08	1996
10009	0.08	1995
10009	0.16	1995
10009	0.14	1995
10009	0.14	1995
10009	0.14	1994
10009	0.14	1994
10009	0.14	1994
10009	0.14	1994
10009	0.14	1993
10009	0.14	1993
10009	0.125	1993
10009	0.125	1993
10009	0.125	1992
10009	0.125	1992
10009	0.125	1992
10009	0.125	1992
10009	0.125	1991
10009	0.125	1991
10009	0.125	1991
10009	0.125	1991
10009	0.125	1990
10009	0.125	1990
10009	0.125	1990
10009	0.125	1989
10009	0.125	1989
10009	0.125	1989
10009	0.125	1989
10009	0.125	1988
10009	0.125	1988
10009	0.1	1988
10009	0.1	1988
10009	0.1	1987
10009	0.1	1987
10009	0.075	1987
10009	0.075	1987
10009	0.12	2000
10014	0.15	1939
10014	0.15	1939
10014	0.25	1939
10014	0.25	1938
10014	0.15	1936
10014	0.15	1936
10014	0.15	1936
10014	0.15	1935
10014	0.15	1935
10014	0.15	1935

Re: HELP: 3 precise and challenging commands

Alpay — Tue, 26 Jun 2012 09:39:50 GMT

Data set seems to be sorted by PERMNO and descending YEAR, but there are multiple records for a given YEAR. How do they get sorted within a year?

Can you provide some examples to the above cases with your data?

e.g.: What record would have 1 for Initiation column for PERMNO 10001?

Re: HELP: 3 precise and challenging commands

spraynardz90 — Tue, 26 Jun 2012 10:40:51 GMT

The dates are sorted by decending year I possess the complete announcement dates (20070630) though for the purposes of this exercise not neccessarily needed. The last observation for any given permno would be the earliest date recorded that the company paid a dividend.

e.g.: What record would have 1 for Initiation column for PERMNO 10001?

For each of the permn numbers the very first record. So for permno 10006 the first 1926 observation recorded would have a 1. and for permno 10001 the first 1986 observation recorded would have a 1.

e.g.: Omission example.

Hypothetically, if permno 10001 paid a dividend in 1990. But there is no record for a dividend payment in 1991, 1992, 1993, 1994 and 1995 (or longer). The record in 1990 would have a 1 coressponding to omission.

e.g.: Change example.

In 1957 permno 1006 paid a dividend of $1, the proceeding year of 1958 this dropped to 0.625. Then the 1958 observation for permno 1006 would have a corresponding 1 value in the change column.

Hope this makes sense!

Re: HELP: 3 precise and challenging commands

Ksharp — Wed, 27 Jun 2012 05:29:53 GMT

data have;
infile cards expandtabs truncover;
input PERMNO     DIVAMT     YEAR ;
cards;
10001     0.16     2007
10001     0.16     2007
10001     0.15     2007
10001     0.14     2006
10001     0.12     2006
10001     0.1     2006
10001     0.08     2006
10001     0.05     2006
10001     0.04     2005
10001     0.135     2003
10001     0.135     2002
10001     0.135     2002
10001     0.135     2002
10001     0.13     2002
10001     0.13     2001
10001     0.13     2001
10001     0.13     2001
10001     0.125     2001
10001     0.125     2000
10001     0.125     2000
10001     0.125     2000
10001     0.12     2000
10001     0.12     1999
10001     0.12     1999
10001     0.12     1999
10001     0.115     1999
10001     0.115     1998
10001     0.115     1998
10001     0.115     1998
10001     0.11     1998
10001     0.11     1997
10001     0.11     1997
10001     0.11     1997
10001     0.105     1997
10001     0.105     1996
10001     0.105     1996
10001     0.105     1996
10001     0.1     1996
10001     0.1     1995
10001     0.1     1995
10001     0.1     1995
10001     0.095     1995
10001     0.095     1994
10001     0.095     1994
10001     0.19     1994
10001     0.175     1994
10001     0.175     1993
10001     0.175     1993
10001     0.175     1993
10001     0.1575     1993
10001     0.1575     1992
10001     0.1575     1992
10001     0.1575     1992
10001     0.15     1992
10001     0.15     1991
10001     0.15     1991
10001     0.15     1991
10001     0.1375     1991
10001     0.1375     1990
10001     0.1375     1990
10001     0.1375     1990
10001     0.125     1990
10001     0.125     1989
10001     0.125     1989
10001     0.12     1989
10001     0.12     1989
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1986
10001     0.105     1986
10001     0.105     1986
10001     0.095     1986
10002     0.01     2009
10002     0.025     2009
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.09     1999
10002     0.09     1999
10002     0.09     1999
10002     0.085     1999
10002     0.085     1998
10002     0.085     1998
10002     0.1275     1998
10002     0.11     1997
10002     0.11     1997
10002     0.1     1997
10002     0.1     1997
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.07     1994
10002     0.07     1994
10002     0.06     1994
10002     0.06     1994
10002     0.05     1993
10002     0.11     1998
10003     0.275     1989
10003     0.25     1989
10003     0.25     1989
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1987
10003     0.1     1987
10006     0.35     1984
10006     0.35     1983
10006     0.35     1983
10006     0.35     1983
10006     0.69     1983
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1981
10006     0.69     1981
10006     0.69     1981
10006     0.625     1981
10006     0.625     1980
10006     0.625     1980
10006     0.625     1980
10006     0.56     1980
10006     0.56     1979
10006     0.56     1979
10006     0.56     1979
10006     0.525     1979
10006     0.525     1978
10006     0.525     1978
10006     0.525     1978
10006     0.5     1978
10006     0.5     1977
10006     0.5     1977
10006     0.5     1977
10006     0.45     1977
10006     0.45     1976
10006     0.45     1976
10006     0.65     1976
10006     0.65     1976
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1974
10006     0.65     1974
10006     0.6     1974
10006     0.6     1974
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1966
10006     0.55     1966
10006     0.55     1966
10006     0.45     1966
10006     0.45     1965
10006     0.75     1965
10006     0.75     1965
10006     0.625     1965
10006     0.625     1964
10006     0.625     1964
10006     0.5     1964
10006     0.4     1964
10006     0.4     1963
10006     0.7     1963
10006     0.7     1963
10006     0.625     1963
10006     0.625     1962
10006     0.625     1962
10006     0.625     1962
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1956
10006     1     1956
10006     1     1956
10006     1     1955
10006     0.25     1931
10006     0.75     1931
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10009     0.12     2000
10009     0.12     2000
10009     0.12     1999
10009     0.12     1999
10009     0.1     1999
10009     0.1     1999
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1997
10009     0.1     1997
10009     0.08     1997
10009     0.08     1997
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1995
10009     0.16     1995
10009     0.14     1995
10009     0.14     1995
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1993
10009     0.14     1993
10009     0.125     1993
10009     0.125     1993
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1990
10009     0.125     1990
10009     0.125     1990
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1988
10009     0.125     1988
10009     0.1     1988
10009     0.1     1988
10009     0.1     1987
10009     0.1     1987
10009     0.075     1987
10009     0.075     1987
10009     0.12     2000
10014     0.15     1939
10014     0.15     1939
10014     0.25     1939
10014     0.25     1938
10014     0.15     1936
10014     0.15     1936
10014     0.15     1936
10014     0.15     1935
10014     0.15     1935
10014     0.15     1935
;
run;
/*First*/
proc sort data=have ;by PERMNO     YEAR;run;
data one; set have;if PERMNO ne lag(PERMNO) then first=1;run;



/*Second*/
proc sql;
 create table two as
  select *,missing((select sum(divamt) from one as b where b.year between a.year+1 and a.year+5 and b.permno=a.permno)) as omission
   from one as a;
quit;

/*Third*/

data three;
 set two;
 retain change;
 if year ne lag(year) then  change=0;
 if year ne lag(year) and _n_ ne 1 and divamt ne lag(divamt) then change=1;
run;

Ksharp

Re: HELP: 3 precise and challenging commands

Alpay — Wed, 27 Jun 2012 06:42:21 GMT

How would you calculate Change when DIVAMT varies within a given year?

In 1963 DIVAMT started at 0.625 and changed to 0.70 and then to 0.40 within the same year. Do these count as Change? What is the DIVAMT for a given year (first/last recorded amount)?

In 1964 DIVAMT started at 0.40 and changed values several times. When compared the last record of 1963 with the first record of 1964 DIVAMT values are the same. What will Change be in this case?

PERMNO

DIVAMT

YEAR

10006	0.45	1965
10006	0.75	1965
10006	0.75	1965
10006	0.625	1965
10006	0.625	1964
10006	0.625	1964
10006	0.5	1964
10006	0.4	1964
10006	0.4	1963
10006	0.7	1963
10006	0.7	1963
10006	0.625	1963

Re: HELP: 3 precise and challenging commands

spraynardz90 — Thu, 28 Jun 2012 04:04:45 GMT

Good question. As dividends can be paid monthly, quarterly or semiannually the amounts paid at each interval might change. I am only interested in yearly patterns.

ie: If company A pays 0.6 in June and 0.8 in December. BUt the following year pays 0.3 in June and 0.9 in December. Then the a value of 1 in the change column should correspond with the 0.3 and 0.9 observations the actual changed observations.

Likewise if company B paid an annual 1.2 dividend in 1963, then the proceeding year it pays four issues of 0.5. Then a 1 should correspond to the 0.5s.

RE: Ksharp thanks very much for your assistance, though with the omission I think I will need to be more precise. Ie: A 1 value should only be associated for 13522 193812.

13450	193812	0
13522	193802	1
13522	193804	1
13522	193812	1
13549	193806	0

Re: HELP: 3 precise and challenging commands

Ksharp — Thu, 28 Jun 2012 05:17:51 GMT

That is easy.

by year notsorted;

if not last.year then omission=0;

But these data doesn't look like what you posted.

what is 13522 ?

Message was edited by: xia keshan

Re: HELP: 3 precise and challenging commands

spraynardz90 — Thu, 28 Jun 2012 05:38:06 GMT

13522 is the permn number, or company identifier.

I have the dividend initiation and omission columns correct. I just need to do dividend change.

For dividend change I need to ignore any changes within the same year. Instead I need to analyse dividend payment patterns in different years. See the example I have provided below. Please note, although there is not a dividend amount change, there is a 1 associated with 195209 and 195301 because there is a semiannual frequency moving to quarterly payment.

permno	divamt	fyear	monthyear	div change
10006	0.25	1931	193104
10006	0.25	1931	193110
10006	1	1937	193706	1
10006	1	1943	194302	1
10006	2	1943	194304
10006	2.5	1943	194310
10006	3	1944	194410	1
10006	3	1945	194510
10006	3	1946	194610
10006	3	1947	194710
10006	3	1948	194810
10006	2	1949	194910	1
10006	0.75	1951	195107	1
10006	0.75	1952	195209	1
10006	0.75	1952	195210
10006	0.75	1953	195301	1
10006	0.75	1953	195304
10006	0.75	1953	195308
10006	0.75	1953	195310
10006	0.75	1954	195401	1
10006	0.75	1954	195404
10006	0.75	1954	195408
10006	0.75	1954	195409
10006	0.75	1954	195412
10006	1	1955	195503	1
10006	1	1955	195507
10006	1	1955	195509

Re: HELP: 3 precise and challenging commands

spraynardz90 — Thu, 28 Jun 2012 05:53:31 GMT

For dividend omission the command doesn't seem to recognise the term "last"

proc sortdata=two;

by year;

quit;

data two; set
two;

if not last year then
omission=0;

quit;

Re: HELP: 3 precise and challenging commands

Ksharp — Thu, 28 Jun 2012 07:53:29 GMT

Do you want change=1 rely on the sum of divamt for the same PERMNO and the same YEAR .

data have;
infile cards expandtabs truncover;
input PERMNO     DIVAMT     YEAR ;
cards;
10001     0.16     2007
10001     0.16     2007
10001     0.15     2007
10001     0.14     2006
10001     0.12     2006
10001     0.1     2006
10001     0.08     2006
10001     0.05     2006
10001     0.04     2005
10001     0.135     2003
10001     0.135     2002
10001     0.135     2002
10001     0.135     2002
10001     0.13     2002
10001     0.13     2001
10001     0.13     2001
10001     0.13     2001
10001     0.125     2001
10001     0.125     2000
10001     0.125     2000
10001     0.125     2000
10001     0.12     2000
10001     0.12     1999
10001     0.12     1999
10001     0.12     1999
10001     0.115     1999
10001     0.115     1998
10001     0.115     1998
10001     0.115     1998
10001     0.11     1998
10001     0.11     1997
10001     0.11     1997
10001     0.11     1997
10001     0.105     1997
10001     0.105     1996
10001     0.105     1996
10001     0.105     1996
10001     0.1     1996
10001     0.1     1995
10001     0.1     1995
10001     0.1     1995
10001     0.095     1995
10001     0.095     1994
10001     0.095     1994
10001     0.19     1994
10001     0.175     1994
10001     0.175     1993
10001     0.175     1993
10001     0.175     1993
10001     0.1575     1993
10001     0.1575     1992
10001     0.1575     1992
10001     0.1575     1992
10001     0.15     1992
10001     0.15     1991
10001     0.15     1991
10001     0.15     1991
10001     0.1375     1991
10001     0.1375     1990
10001     0.1375     1990
10001     0.1375     1990
10001     0.125     1990
10001     0.125     1989
10001     0.125     1989
10001     0.12     1989
10001     0.12     1989
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1986
10001     0.105     1986
10001     0.105     1986
10001     0.095     1986
10002     0.01     2009
10002     0.025     2009
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.09     1999
10002     0.09     1999
10002     0.09     1999
10002     0.085     1999
10002     0.085     1998
10002     0.085     1998
10002     0.1275     1998
10002     0.11     1997
10002     0.11     1997
10002     0.1     1997
10002     0.1     1997
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.07     1994
10002     0.07     1994
10002     0.06     1994
10002     0.06     1994
10002     0.05     1993
10002     0.11     1998
10003     0.275     1989
10003     0.25     1989
10003     0.25     1989
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1987
10003     0.1     1987
10006     0.35     1984
10006     0.35     1983
10006     0.35     1983
10006     0.35     1983
10006     0.69     1983
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1981
10006     0.69     1981
10006     0.69     1981
10006     0.625     1981
10006     0.625     1980
10006     0.625     1980
10006     0.625     1980
10006     0.56     1980
10006     0.56     1979
10006     0.56     1979
10006     0.56     1979
10006     0.525     1979
10006     0.525     1978
10006     0.525     1978
10006     0.525     1978
10006     0.5     1978
10006     0.5     1977
10006     0.5     1977
10006     0.5     1977
10006     0.45     1977
10006     0.45     1976
10006     0.45     1976
10006     0.65     1976
10006     0.65     1976
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1974
10006     0.65     1974
10006     0.6     1974
10006     0.6     1974
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1966
10006     0.55     1966
10006     0.55     1966
10006     0.45     1966
10006     0.45     1965
10006     0.75     1965
10006     0.75     1965
10006     0.625     1965
10006     0.625     1964
10006     0.625     1964
10006     0.5     1964
10006     0.4     1964
10006     0.4     1963
10006     0.7     1963
10006     0.7     1963
10006     0.625     1963
10006     0.625     1962
10006     0.625     1962
10006     0.625     1962
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1956
10006     1     1956
10006     1     1956
10006     1     1955
10006     0.25     1931
10006     0.75     1931
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10009     0.12     2000
10009     0.12     2000
10009     0.12     1999
10009     0.12     1999
10009     0.1     1999
10009     0.1     1999
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1997
10009     0.1     1997
10009     0.08     1997
10009     0.08     1997
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1995
10009     0.16     1995
10009     0.14     1995
10009     0.14     1995
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1993
10009     0.14     1993
10009     0.125     1993
10009     0.125     1993
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1990
10009     0.125     1990
10009     0.125     1990
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1988
10009     0.125     1988
10009     0.1     1988
10009     0.1     1988
10009     0.1     1987
10009     0.1     1987
10009     0.075     1987
10009     0.075     1987
10009     0.12     2000
10014     0.15     1939
10014     0.15     1939
10014     0.25     1939
10014     0.25     1938
10014     0.15     1936
10014     0.15     1936
10014     0.15     1936
10014     0.15     1935
10014     0.15     1935
10014     0.15     1935
;
run;
/*First*/
proc sort data=have ;by PERMNO     YEAR;run;
data one; set have;if PERMNO ne lag(PERMNO) then first=1;run;



/*Second*/
proc sql;
 create table two as
  select *,missing((select sum(divamt) from one as b where b.year between a.year+1 and a.year+5 and b.permno=a.permno)) as omission
   from one as a;
quit;
data two;
 set two;
 by year notsorted;
if not last.year then omission=0;
run;

/*Third*/

proc sql;
 create table temp as
  select PERMNO,YEAR,sum(divamt) as sum
   from one
    group by PERMNO,YEAR
     order by PERMNO,YEAR;
quit;
data temp(drop=sum);
 set temp;
 if sum ne lag(sum) and _n_ ne 1 then change=1;
run;
data three;
 merge two temp;
 by PERMNO YEAR;
 output;
 call missing(of _all_);
run;

Ksharp

Re: HELP: 3 precise and challenging commands

spraynardz90 — Mon, 02 Jul 2012 06:38:10 GMT

Thanks very much!

Could it be possible to further break down dividend change to be more specific?

I've noticed firms can either increase size(1), decrease size(2), increase frequency(3) or decrease frequency(4).

So if the increase in dividend from t and t-1, is greater than 5% of the t-1 dividend, have a 1 in the increase column, associated with the first coresponding dividend for time t.

Whereas, if the decrease in dividend from t and t-1, is reduced by 5% of the t-1 dividend, have a 1 in the decrease column, associated with the first coresponding dividend for time t.

And if the freqency increases 1 coresponds with increase frequency column (vise versa for decrease).

It becomes quite complicated on SAS do you think manually is a safer option?

Re: HELP: 3 precise and challenging commands

Ksharp — Mon, 02 Jul 2012 09:40:06 GMT

OK. No problem. But I don't understand the following ,what is frequency ?

And if the freqency increases 1 coresponds with increase frequency column (vise versa for decrease).

So I only process the first one.

data have;
infile cards expandtabs truncover;
input PERMNO     DIVAMT     YEAR ;
cards;
10001     0.16     2007
10001     0.16     2007
10001     0.15     2007
10001     0.14     2006
10001     0.12     2006
10001     0.1     2006
10001     0.08     2006
10001     0.05     2006
10001     0.04     2005
10001     0.135     2003
10001     0.135     2002
10001     0.135     2002
10001     0.135     2002
10001     0.13     2002
10001     0.13     2001
10001     0.13     2001
10001     0.13     2001
10001     0.125     2001
10001     0.125     2000
10001     0.125     2000
10001     0.125     2000
10001     0.12     2000
10001     0.12     1999
10001     0.12     1999
10001     0.12     1999
10001     0.115     1999
10001     0.115     1998
10001     0.115     1998
10001     0.115     1998
10001     0.11     1998
10001     0.11     1997
10001     0.11     1997
10001     0.11     1997
10001     0.105     1997
10001     0.105     1996
10001     0.105     1996
10001     0.105     1996
10001     0.1     1996
10001     0.1     1995
10001     0.1     1995
10001     0.1     1995
10001     0.095     1995
10001     0.095     1994
10001     0.095     1994
10001     0.19     1994
10001     0.175     1994
10001     0.175     1993
10001     0.175     1993
10001     0.175     1993
10001     0.1575     1993
10001     0.1575     1992
10001     0.1575     1992
10001     0.1575     1992
10001     0.15     1992
10001     0.15     1991
10001     0.15     1991
10001     0.15     1991
10001     0.1375     1991
10001     0.1375     1990
10001     0.1375     1990
10001     0.1375     1990
10001     0.125     1990
10001     0.125     1989
10001     0.125     1989
10001     0.12     1989
10001     0.12     1989
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1986
10001     0.105     1986
10001     0.105     1986
10001     0.095     1986
10002     0.01     2009
10002     0.025     2009
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.09     1999
10002     0.09     1999
10002     0.09     1999
10002     0.085     1999
10002     0.085     1998
10002     0.085     1998
10002     0.1275     1998
10002     0.11     1997
10002     0.11     1997
10002     0.1     1997
10002     0.1     1997
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.07     1994
10002     0.07     1994
10002     0.06     1994
10002     0.06     1994
10002     0.05     1993
10002     0.11     1998
10003     0.275     1989
10003     0.25     1989
10003     0.25     1989
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1987
10003     0.1     1987
10006     0.35     1984
10006     0.35     1983
10006     0.35     1983
10006     0.35     1983
10006     0.69     1983
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1981
10006     0.69     1981
10006     0.69     1981
10006     0.625     1981
10006     0.625     1980
10006     0.625     1980
10006     0.625     1980
10006     0.56     1980
10006     0.56     1979
10006     0.56     1979
10006     0.56     1979
10006     0.525     1979
10006     0.525     1978
10006     0.525     1978
10006     0.525     1978
10006     0.5     1978
10006     0.5     1977
10006     0.5     1977
10006     0.5     1977
10006     0.45     1977
10006     0.45     1976
10006     0.45     1976
10006     0.65     1976
10006     0.65     1976
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1974
10006     0.65     1974
10006     0.6     1974
10006     0.6     1974
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1966
10006     0.55     1966
10006     0.55     1966
10006     0.45     1966
10006     0.45     1965
10006     0.75     1965
10006     0.75     1965
10006     0.625     1965
10006     0.625     1964
10006     0.625     1964
10006     0.5     1964
10006     0.4     1964
10006     0.4     1963
10006     0.7     1963
10006     0.7     1963
10006     0.625     1963
10006     0.625     1962
10006     0.625     1962
10006     0.625     1962
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1956
10006     1     1956
10006     1     1956
10006     1     1955
10006     0.25     1931
10006     0.75     1931
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10009     0.12     2000
10009     0.12     2000
10009     0.12     1999
10009     0.12     1999
10009     0.1     1999
10009     0.1     1999
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1997
10009     0.1     1997
10009     0.08     1997
10009     0.08     1997
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1995
10009     0.16     1995
10009     0.14     1995
10009     0.14     1995
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1993
10009     0.14     1993
10009     0.125     1993
10009     0.125     1993
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1990
10009     0.125     1990
10009     0.125     1990
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1988
10009     0.125     1988
10009     0.1     1988
10009     0.1     1988
10009     0.1     1987
10009     0.1     1987
10009     0.075     1987
10009     0.075     1987
10009     0.12     2000
10014     0.15     1939
10014     0.15     1939
10014     0.25     1939
10014     0.25     1938
10014     0.15     1936
10014     0.15     1936
10014     0.15     1936
10014     0.15     1935
10014     0.15     1935
10014     0.15     1935
;
run;
/*First*/
proc sort data=have ;by PERMNO     YEAR;run;
data one; set have;if PERMNO ne lag(PERMNO) then first=1;run;



/*Second*/
proc sql;
 create table two as
  select *,missing((select sum(divamt) from one as b where b.year between a.year+1 and a.year+5 and b.permno=a.permno)) as omission
   from one as a;
quit;
data two;
 set two;
 by year notsorted;
if not last.year then omission=0;
run;

/*Third*/

proc sql;
 create table temp as
  select PERMNO,YEAR,sum(divamt) as sum
   from one
    group by PERMNO,YEAR
     order by PERMNO,YEAR;
quit;
data temp;
 set temp;
 if sum ne lag(sum) and _n_ ne 1 then change=1;
run;
data three;
 merge two temp;
 by PERMNO YEAR;
 output;
 call missing(of _all_);
run;

/*Fourth*/

proc sql;
create table four as
 select *,case when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno) ) gt 0.05 then 1
            else . end as increase,
         case when  missing((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) then .
               when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) lt -0.05 then 1
                else . end as decrease

  from three as a ;
quit;

Ksharp

Re: HELP: 3 precise and challenging commands

spraynardz90 — Mon, 02 Jul 2012 11:20:26 GMT

Thanks Ksharp does the 4th command recognise that it can only be classified as a dividend increase if the dividend has increased in size by at least 5% since last year? Likewise it can only be recognised as a dividend decrease if the dividend amount has decreased by at least 5% since the previous year?

In response to frequency, this means the regularity of the payment itself. For example: can you see that 75c dividend was paid once in 1951, then two payments occured in 1952, four payments in 1953 and 5 times in 1954? I was hoping there might be a SAS command to do this ie: 1 in the freq increase column on the first record for the year where the dividend has increased in regularity of payment. (Vice versa for the decrease freq column)

10006	0.75	1951	195107
10006	0.75	1952	195209
10006	0.75	1952	195210
10006	0.75	1953	195301
10006	0.75	1953	195304
10006	0.75	1953	195308
10006	0.75	1953	195310
10006	0.75	1954	195401
10006	0.75	1954	195404
10006	0.75	1954	195408
10006	0.75	1954	195409
10006	0.75	1954	195412

Re: HELP: 3 precise and challenging commands

Ksharp — Tue, 03 Jul 2012 03:36:47 GMT

"does the 4th command recognise that it can only be classified as a dividend increase if the dividend has increased in size by at least 5% since last year?"

Yes . I concerned the sum of divamt for year t and year t-1 . That means My third one might be wrong since I only concern the previous year (may not be year t-1), But I rectified it ,see the below. and about Freq it is also done.

data have;
infile cards expandtabs truncover;
input PERMNO     DIVAMT     YEAR ;
cards;
10001     0.16     2007
10001     0.16     2007
10001     0.15     2007
10001     0.14     2006
10001     0.12     2006
10001     0.1     2006
10001     0.08     2006
10001     0.05     2006
10001     0.04     2005
10001     0.135     2003
10001     0.135     2002
10001     0.135     2002
10001     0.135     2002
10001     0.13     2002
10001     0.13     2001
10001     0.13     2001
10001     0.13     2001
10001     0.125     2001
10001     0.125     2000
10001     0.125     2000
10001     0.125     2000
10001     0.12     2000
10001     0.12     1999
10001     0.12     1999
10001     0.12     1999
10001     0.115     1999
10001     0.115     1998
10001     0.115     1998
10001     0.115     1998
10001     0.11     1998
10001     0.11     1997
10001     0.11     1997
10001     0.11     1997
10001     0.105     1997
10001     0.105     1996
10001     0.105     1996
10001     0.105     1996
10001     0.1     1996
10001     0.1     1995
10001     0.1     1995
10001     0.1     1995
10001     0.095     1995
10001     0.095     1994
10001     0.095     1994
10001     0.19     1994
10001     0.175     1994
10001     0.175     1993
10001     0.175     1993
10001     0.175     1993
10001     0.1575     1993
10001     0.1575     1992
10001     0.1575     1992
10001     0.1575     1992
10001     0.15     1992
10001     0.15     1991
10001     0.15     1991
10001     0.15     1991
10001     0.1375     1991
10001     0.1375     1990
10001     0.1375     1990
10001     0.1375     1990
10001     0.125     1990
10001     0.125     1989
10001     0.125     1989
10001     0.12     1989
10001     0.12     1989
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.11     1988
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1987
10001     0.105     1986
10001     0.105     1986
10001     0.105     1986
10001     0.095     1986
10002     0.01     2009
10002     0.025     2009
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2008
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2007
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2006
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2005
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2004
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.13     2003
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.12     2002
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.11     2001
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.1     2000
10002     0.09     1999
10002     0.09     1999
10002     0.09     1999
10002     0.085     1999
10002     0.085     1998
10002     0.085     1998
10002     0.1275     1998
10002     0.11     1997
10002     0.11     1997
10002     0.1     1997
10002     0.1     1997
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.1     1996
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.08     1995
10002     0.07     1994
10002     0.07     1994
10002     0.06     1994
10002     0.06     1994
10002     0.05     1993
10002     0.11     1998
10003     0.275     1989
10003     0.25     1989
10003     0.25     1989
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1988
10003     0.25     1987
10003     0.1     1987
10006     0.35     1984
10006     0.35     1983
10006     0.35     1983
10006     0.35     1983
10006     0.69     1983
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1982
10006     0.69     1981
10006     0.69     1981
10006     0.69     1981
10006     0.625     1981
10006     0.625     1980
10006     0.625     1980
10006     0.625     1980
10006     0.56     1980
10006     0.56     1979
10006     0.56     1979
10006     0.56     1979
10006     0.525     1979
10006     0.525     1978
10006     0.525     1978
10006     0.525     1978
10006     0.5     1978
10006     0.5     1977
10006     0.5     1977
10006     0.5     1977
10006     0.45     1977
10006     0.45     1976
10006     0.45     1976
10006     0.65     1976
10006     0.65     1976
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1975
10006     0.65     1974
10006     0.65     1974
10006     0.6     1974
10006     0.6     1974
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1973
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1972
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1971
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1970
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1969
10006     0.6     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1968
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1967
10006     0.55     1966
10006     0.55     1966
10006     0.55     1966
10006     0.45     1966
10006     0.45     1965
10006     0.75     1965
10006     0.75     1965
10006     0.625     1965
10006     0.625     1964
10006     0.625     1964
10006     0.5     1964
10006     0.4     1964
10006     0.4     1963
10006     0.7     1963
10006     0.7     1963
10006     0.625     1963
10006     0.625     1962
10006     0.625     1962
10006     0.625     1962
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1961
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1960
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1959
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     0.625     1958
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1957
10006     1     1956
10006     1     1956
10006     1     1956
10006     1     1955
10006     0.25     1931
10006     0.75     1931
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1930
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1929
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1928
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1927
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10006     1.5     1926
10009     0.12     2000
10009     0.12     2000
10009     0.12     1999
10009     0.12     1999
10009     0.1     1999
10009     0.1     1999
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1998
10009     0.1     1997
10009     0.1     1997
10009     0.08     1997
10009     0.08     1997
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1996
10009     0.08     1995
10009     0.16     1995
10009     0.14     1995
10009     0.14     1995
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1994
10009     0.14     1993
10009     0.14     1993
10009     0.125     1993
10009     0.125     1993
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1992
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1991
10009     0.125     1990
10009     0.125     1990
10009     0.125     1990
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1989
10009     0.125     1988
10009     0.125     1988
10009     0.1     1988
10009     0.1     1988
10009     0.1     1987
10009     0.1     1987
10009     0.075     1987
10009     0.075     1987
10009     0.12     2000
10014     0.15     1939
10014     0.15     1939
10014     0.25     1939
10014     0.25     1938
10014     0.15     1936
10014     0.15     1936
10014     0.15     1936
10014     0.15     1935
10014     0.15     1935
10014     0.15     1935
;
run;
/*First*/
proc sort data=have ;by PERMNO     YEAR;run;
data one; set have;if PERMNO ne lag(PERMNO) then first=1;run;



/*Second*/
proc sql;
 create table two as
  select *,missing((select sum(divamt) from one as b where b.year between a.year+1 and a.year+5 and b.permno=a.permno)) as omission
   from one as a;
quit;
data two;
 set two;
 by year notsorted;
if not last.year then omission=0;
run;

/*Third---------TroubleShooting  */

proc sql;
 create table temp as
  select *,sum(divamt) as sum
   from one
    group by PERMNO,YEAR
     order by PERMNO,YEAR;
quit;
data three;
 set temp;
 if sum ne lag(sum) and year eq lag(year)+1  then change=1;
run;


/*Fourth*/

proc sql;
create table four as
 select *,case when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno) ) gt 0.05 then 1
            else . end as increase,
         case when  missing((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) then .
               when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) lt -0.05 then 1
                else . end as decrease

  from three as a ;
quit;

/*Fifth*/
proc sql;
 create table temp1 as
  select *,count(*) as freq
   from one
    group by PERMNO,YEAR
     order by PERMNO,YEAR;
quit;
data five;
 set temp1;
 if freq gt lag(freq) and year eq lag(year)+1  then increase=1;
  else if freq lt lag(freq) and year eq lag(year)+1  then decrease=1;
run;

Ksharp

Re: HELP: 3 precise and challenging commands

spraynardz90 — Wed, 04 Jul 2012 11:23:03 GMT

Hi Ksharp

Seem to be experiencing a problem with the increase and decrease columns for dividend size. As the snapshot above reveals. See there how there is a single 1 for change, this is correct. But this 1 should pair with another 1 in either the increase or decrease column. Also actual increases in the dividend are being classified as decreases according to the command.

How can I fix for this?

Re: HELP: 3 precise and challenging commands

Ksharp — Wed, 04 Jul 2012 15:27:54 GMT

Opps. I changed the Third code, so it will render the wrong result for the original the fourth code.

/*Fourth*/
data three;
 set three;
 by PERMNO YEAR notsorted;
 if not first.year then call missing(sum);
run;
proc sql;
create table four as
 select *,case when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno) ) gt 0.05 then 1
            else . end as increase,
         case when  missing((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) then .
               when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) lt -0.05 then 1
                else . end as decrease

  from three as a ;
quit;

Ksharp

Re: HELP: 3 precise and challenging commands

spraynardz90 — Thu, 05 Jul 2012 02:56:27 GMT

I think there might be a slight problem.

With this new code sas performs the command very quickly and I just get blanks in the increase and decrease columns (see the screenshot below).

All the other commands work very well but for step four the 1 value in the "change" columns should correspond to either an increase or decrease.

Re: HELP: 3 precise and challenging commands

Ksharp — Thu, 05 Jul 2012 03:14:46 GMT

"step four the 1 value in the "change" columns should correspond to either an increase or decrease."\

But you said flagging 1 when increasing greater than 0.05 and flagging 1 when decreasing less than -0.05.

And my output is not like yours .

/*First*/
proc sort data=have ;by PERMNO     YEAR;run;
data one; set have;if PERMNO ne lag(PERMNO) then first=1;run;



/*Second*/
proc sql;
 create table two as
  select *,missing((select sum(divamt) from one as b where b.year between a.year+1 and a.year+5 and b.permno=a.permno)) as omission
   from one as a;
quit;
data two;
 set two;
 by year notsorted;
if not last.year then omission=0;
run;

/*Third---------TroubleShooting  */

proc sql;
 create table temp as
  select *,sum(divamt) as sum
   from one
    group by PERMNO,YEAR
     order by PERMNO,YEAR;
quit;
data three;
 set temp;
 if sum ne lag(sum) and year eq lag(year)+1  then change=1;
run;


/*Fourth*/
data three;
 set three;
 by PERMNO YEAR notsorted;
 if not first.year then call missing(sum);
run;
proc sql;
create table four as
 select *,case when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno) ) gt 0.05 then 1
            else . end as increase,
         case when  missing((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) then .
               when ((sum-(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno))/(select sum(sum) from three as b where b.year = a.year-1 and b.permno=a.permno)) lt -0.05 then 1
                else . end as decrease

  from three as a ;
quit;

Ksharp

Re: HELP: 3 precise and challenging commands

spraynardz90 — Thu, 05 Jul 2012 03:25:00 GMT

YES it is working correctly! Many thanks

Re: HELP: 3 precise and challenging commands

spraynardz90 — Sun, 22 Jul 2012 05:31:47 GMT

Hi again Ksharp

I may need to re-work some of my recognition criteria. Do you think commands are possible for the following?

(1) Initiation & Omission. T=0 is the year of the dividend payment, if there is no dividend recorded at T+1 then the observation at T0 has a 1 in the omission column. For example; In 2000 a dividend of 50c was paid by PERMNO 10003 and there is no record of a dividend in 2001. So a 1 occurs in the omission column for the 50c dividend observation for permno 10003.

Similarly, if no dividend is recorded at t-1 then a 1 in the initiation column corresponds to the t=0 event. Referring to the same example. A 50c dividend is paid by permno 10003 in 2000, but there is no observation recorded for 1999 then a 1 in the initiation column corresponds to the 2000 observation.

(2) Increase & Decrease (SIZE). If the dividend paid at t=0 exceeds the maximum dividend recorded by the same PERMNO at t-1 by 20% then a 1 corresponds to the increase column at t=0. For example PERMNO 18001 pays a 2.5 dividend in 2005, the div amt recorded for PERMNO 18001 in 2004 is 0.7, 0.8 and 1. As 2.5>1 (the maximum dividend recorded one yr prior) then a 1 in the increase column corresponds to the 2005 observation.

If the dividend paid at t=0 is less than the minimum dividend recorded by the same PERMNO at t-1 by 20% then a 1 corresponds to the decrease column at t=0. For example PERMNO 18001 pays a 0.1 dividend in 2005, the div amt recorded for PERMNO 18001 in 2004 is 0.7, 0.8 and 1. As 0.1<0.7 (the minimum dividend recorded one yr prior) then a 1 in the decrease column corresponds to the 2005 observation.

Sorry to bother you with this, but having difficultly with part (2). Thanking you in advance