Solved: Re: HELP: 3 precise and challenging commands - Page 2

Ksharp · Posted 07-04-2012 11:27 AM

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

spraynardz90 · Posted 07-04-2012 10:56 PM

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.

Ksharp · Posted 07-04-2012 11:14 PM

"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

spraynardz90 · Posted 07-04-2012 11:25 PM

YES it is working correctly! Many thanks

spraynardz90 · Posted 07-22-2012 01:31 AM

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

Ksharp · Posted 07-23-2012 12:49 AM

Of course, No problem.

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;

proc sql;
create table want as
 select *,case when (select count(year) from have as b where b.YEAR=a.YEAR-1 and b.PERMNO=a.PERMNO) gt 0 then 0 else 1 end as initiation ,
          case when (select count(year) from have as b where b.YEAR=a.YEAR+1 and b.PERMNO=a.PERMNO) gt 0 then 0 else 1 end as omission ,
          case when (select max(DIVAMT)*1.2 from have as b where b.YEAR=a.YEAR-1 and b.PERMNO=a.PERMNO) lt a.DIVAMT and calculated initiation ne 1 then 1 else 0 end as increase , 
          case when (select min(DIVAMT)*0.8 from have as b where b.YEAR=a.YEAR-1 and b.PERMNO=a.PERMNO) gt a.DIVAMT and calculated initiation ne 1 then 1 else 0 end as decrease   
  from have as a;
quit;

Ksharp

spraynardz90 · Posted 07-23-2012 11:11 PM

Thanks

I notice it will associate a initiation (omission) to ALL the dividends paid in that beginning(final) year.

For initiation can it please just recognise the first 1935 that appears

For omission can it please just recognise the last 1935 that appears

Rather than associating a 1 to every 1935 dividend payment, so if they paid quarterly there would be four 1s in initiation. Can we please just have 1 initiation for the first observation in that yr?

Ksharp · Posted 07-24-2012 12:11 AM

OK.

proc sql;
create table want as
 select *,case when (select count(year) from have as b where b.YEAR=a.YEAR-1 and b.PERMNO=a.PERMNO) gt 0 then 0 else 1 end as initiation ,
          case when (select count(year) from have as b where b.YEAR=a.YEAR+1 and b.PERMNO=a.PERMNO) gt 0 then 0 else 1 end as omission ,
          case when (select max(DIVAMT)*1.2 from have as b where b.YEAR=a.YEAR-1 and b.PERMNO=a.PERMNO) lt a.DIVAMT and calculated initiation ne 1 then 1 else 0 end as increase , 
          case when (select min(DIVAMT)*0.8 from have as b where b.YEAR=a.YEAR-1 and b.PERMNO=a.PERMNO) gt a.DIVAMT and calculated initiation ne 1 then 1 else 0 end as decrease   
  from have as a
   order by PERMNO,YEAR ;
quit;
data want;
 set want;
 by PERMNO YEAR ;
 if not first.YEAR then initiation=0 ;
 if not last.YEAR then omission=0;
run;

Ksharp

totomkos · Posted 11-26-2012 12:52 PM

Hi Ksharp,

I would be glad if you could help me with my problem. I work on dividend omissions and i read your posts, my problem is that i want to create my omissions sample and take itno account the following : omissions to allow for "late" payments between the last declaration date and define a 3-year period to consist of 1128 days or approximately 31 days in a month. What i mean is that i define an omission of the company pays no dividend after the last declaration or stop paying dividend for 1128 days. How can i add these restriction to my sample??? i use your code and i need something more....

thank you in advance.

example

permno dclrdt

10006 19650903

10006 196501204

10006 19690105

this is an omission as betwen the last and the previous there are more than 1128 days....

art297 · Posted 11-26-2012 01:05 PM

totomkos: suggestion: create a new thread and describe your problem in more detail along with the code you have already tried.

Ksharp · Posted 11-26-2012 09:52 PM

As ArthurT suggested,Start a new session, post your sample data and the result you need, and explained it in detail.

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away