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 |
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
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?
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!
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
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 |
Hi
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 |
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
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 |
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;
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
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?
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
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 |
"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
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.