|
Hi,
Thanks to everyone that has helped clearing up the first.variable
problem. We have eliminated that error also and yet the code still
does not execute according to expectations. The following outline may
be helpful:
1) a brief statement of what we want to do
2) a step by step code summary. showing the code and explaining what
we expect to see happen
3) output from put _all_
4) output of what we WOULD LIKE TO SEE
5) Output that we are getting;
1)I have for each month dividends,
>purchases, and sales. These values are correctly recorded. I want to
>create a starting cash balance and an ending cash balance for each
>month. The first starting cash balance is 0 and the ending cash
>balance is the sum of div + purc + sales + the starting cash balance.
>
>The next starting cash balance is the previous ending cash balance and
>the ending cash balance is the starting cash value + div + purch +
>sales. We continue this through all of our months.
it is important to understand how our data set is laid out:
we have
date1 ticker1
date2 ticker1
.
.
date5 ticker1
date1 ticker2
date2 ticker2
.
.
.
so with 40 tickers and 5 dates we have 200 observations with a
startshare and endshare for each observation. Into this dataset when
ticker ='*$$$' we want to adjust the values of startshare and endshare
as described above.
2) the code:
Our first statement:
if ticker ='*$$$' then do;
here we are only going to be looking at the records that have *$$$ as
the ticker.
If first.ticker then startshare = 0;
Here when we hit the first loop and first.ticker=1 we set startshare to
0
Else startshare = endshare;
We get here if first.ticker = 0 which is on the 2,3,4 and 5 cycle. At
this point the retain statement should be coming into play
And then finaly for all observations we set the new endshare value with
Endshare = startshare + tot_div + tot_sales+ tot_purchases;
3) actual code with put _all_ output:
665 data cmp_7;
666 set cmp_6;
667 by ticker date ;
668 retain endshare;
669
670 If ticker = '*$$$' then do ;
671
672 put _all_;
673
674 if first.ticker then startshare =0;
675 ** startshare depends on retained value of endshare;
676 else startshare = endshare;
677 ** endshare depends on new value of startshare;
678 endshare = startshare + tot_div + tot_sales +
tot_purchases;
679
680 put _all_;
681
682 end;
683 else; some people say this else statement shouldn't be left
hanging here but what we are doing is going through the ticker list and
only doing something when ticker = '*$$$'
684 run;
DATE=2002-12-31 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=-9617604
endshare=-9617604 startshare=0
dol_div=0 dol_sales=9617604 dol_purchases=0 _TYPE_=0 _FREQ_=40
tot_div=0 tot_sales=9617604
tot_purchases=-9617604 FIRST.TICKER=1 LAST.TICKER=0 FIRST.DATE=1
LAST.DATE=1 startshre=. _ERROR_=0
_N_=1
DATE=2002-12-31 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=-9617604
endshare=0 startshare=0 dol_div=0 dol_sales=9617604 dol_purchases=0
_TYPE_=0 _FREQ_=40 tot_div=0 tot_sales=9617604
tot_purchases=-9617604 FIRST.TICKER=1 LAST.TICKER=0 FIRST.DATE=1
LAST.DATE=1 startshre=0 _ERROR_=0
_N_=1
DATE=2003-01-31 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9617604 startshare=-9617604
dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0 _FREQ_=40
tot_div=17028.5 tot_sales=789484
tot_purchases=-991358 FIRST.TICKER=0 LAST.TICKER=0 FIRST.DATE=1
LAST.DATE=1 startshre=. _ERROR_=0
_N_=2
DATE=2003-01-31 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9802449.5
startshare=-9617604 dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0
_FREQ_=40 tot_div=17028.5
tot_sales=789484 tot_purchases=-991358 FIRST.TICKER=0 LAST.TICKER=0
FIRST.DATE=1 LAST.DATE=1
startshre=. _ERROR_=0 _N_=2
DATE=2003-02-28 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9617604 startshare=-9617604
dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0 _FREQ_=40
tot_div=20471.25 tot_sales=667704
tot_purchases=-687500 FIRST.TICKER=0 LAST.TICKER=0 FIRST.DATE=1
LAST.DATE=1 startshre=. _ERROR_=0
_N_=3
DATE=2003-02-28 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9616928.75
startshare=-9617604 dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0
_FREQ_=40 tot_div=20471.25
tot_sales=667704 tot_purchases=-687500 FIRST.TICKER=0 LAST.TICKER=0
FIRST.DATE=1 LAST.DATE=1
startshre=. _ERROR_=0 _N_=3
DATE=2003-03-31 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9617604 startshare=-9617604
dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0 _FREQ_=40
tot_div=12244.835 tot_sales=760469
tot_purchases=-729916 FIRST.TICKER=0 LAST.TICKER=0 FIRST.DATE=1
LAST.DATE=1 startshre=. _ERROR_=0
_N_=4
DATE=2003-03-31 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9574806.165
startshare=-9617604 dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0
_FREQ_=40 tot_div=12244.835
tot_sales=760469 tot_purchases=-729916 FIRST.TICKER=0 LAST.TICKER=0
FIRST.DATE=1 LAST.DATE=1
startshre=. _ERROR_=0 _N_=4
DATE=2003-04-30 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9617604 startshare=-9617604
dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0 _FREQ_=40
tot_div=12764.5 tot_sales=0
tot_purchases=0 FIRST.TICKER=0 LAST.TICKER=1 FIRST.DATE=1 LAST.DATE=1
startshre=. _ERROR_=0 _N_=5
DATE=2003-04-30 TICKER=*$$$ PRICE=1 DIV=0 SPLIT_FAC=1 trade=0
endshare=-9604839.5
startshare=-9617604 dol_div=0 dol_sales=0 dol_purchases=0 _TYPE_=0
_FREQ_=40 tot_div=12764.5 tot_sales=0 tot_purchases=0 FIRST.TICKER=0
LAST.TICKER=1 FIRST.DATE=1
LAST.DATE=1 startshre=.
_ERROR_=0 _N_=5
NOTE: There were 200 observations read from the data set WORK.CMP_6.
NOTE: The data set WORK.CMP_7 has 200 observations and 17 variables.
NOTE: DATA statement used:
real time 0.01 seconds
cpu time 0.01 seconds
(PLEASE NOTE THE STARTSHRE ERROR WAS CORRECTED AND WAS JUST A SPEELING
ERROR)
4)output we would like to see
Obs TICKER startshare endshare PRICE tot_div sales
purchases
1 *$$$ 0 0 1 0 9617604
-9617604
2 *$$$ 0 -184845.50 1 17028.50 789484
-991358
3 *$$$ -184845.50 -184170.25 1 20471.25 667704
-687500
4 *$$$ -184170.25 -141372.41 1 12244.84 760469
-729916
5 *$$$ -141372.41 -128607.91 1 12764.50 0
0
5) But the output I am getting is:
Obs TICKER startshare endshare PRICE tot_div sales
purchases
1 *$$$ 0 0.00 1 0.00 9617604
-9617604
2 *$$$ -9617604 -9802449.50 1 17028.50 789484
-991358
3 *$$$ -9617604 -9616928.75 1 20471.25 667704
-687500
4 *$$$ -9617604 -9574806.17 1 12244.84 760469
-729916
5 *$$$ -9617604 -9604839.50 1 12764.50 0
0
| 
