Hi Everyone,
I have the following dataset:
isin | date | price | event_day | calday |
FI0000000001 | 22/09/1997 | 23.2 | 13/10/1997 | -15 |
FI0000000001 | 23/09/1997 | 22.7 | 13/10/1997 | -14 |
FI0000000001 | 24/09/1997 | 22.5 | 13/10/1997 | -13 |
FI0000000001 | 25/09/1997 | 23 | 13/10/1997 | -12 |
FI0000000001 | 26/09/1997 | 24 | 13/10/1997 | -11 |
FI0000000001 | 29/09/1997 | 25 | 13/10/1997 | -10 |
FI0000000001 | 30/09/1997 | 25.1 | 13/10/1997 | -9 |
FI0000000001 | 1/10/1997 | 24.7 | 13/10/1997 | -8 |
FI0000000001 | 2/10/1997 | 25.4 | 13/10/1997 | -7 |
FI0000000001 | 3/10/1997 | 26.5 | 13/10/1997 | -6 |
FI0000000001 | 6/10/1997 | 27 | 13/10/1997 | -5 |
FI0000000001 | 7/10/1997 | 26.7 | 13/10/1997 | -4 |
FI0000000001 | 8/10/1997 | 26.7 | 13/10/1997 | -3 |
FI0000000001 | 9/10/1997 | 25.1 | 13/10/1997 | -2 |
FI0000000001 | 10/10/1997 | 25.2 | 13/10/1997 | -1 |
FI0000000001 | 13/10/1997 | 28.1 | 13/10/1997 | 0 |
FI0000000001 | 14/10/1997 | 27.2 | 13/10/1997 | 1 |
FI0000000001 | 15/10/1997 | 26.4 | 13/10/1997 | 2 |
FI0000000001 | 16/10/1997 | 26.5 | 13/10/1997 | 3 |
FI0000000001 | 17/10/1997 | 26.3 | 13/10/1997 | 4 |
FI0000000001 | 20/10/1997 | 26.9 | 13/10/1997 | 5 |
FI0000000001 | 30/08/1999 | 5.15 | 20/09/1999 | -15 |
FI0000000001 | 31/08/1999 | 5 | 20/09/1999 | -14 |
FI0000000001 | 1/09/1999 | 4.99 | 20/09/1999 | -13 |
FI0000000001 | 2/09/1999 | 4.93 | 20/09/1999 | -12 |
FI0000000001 | 3/09/1999 | 4.97 | 20/09/1999 | -11 |
FI0000000001 | 6/09/1999 | 5.04 | 20/09/1999 | -10 |
FI0000000001 | 7/09/1999 | 5.17 | 20/09/1999 | -9 |
FI0000000001 | 8/09/1999 | 5.03 | 20/09/1999 | -8 |
FI0000000001 | 9/09/1999 | 5.03 | 20/09/1999 | -7 |
FI0000000001 | 10/09/1999 | 4.98 | 20/09/1999 | -6 |
FI0000000001 | 13/09/1999 | 4.98 | 20/09/1999 | -5 |
FI0000000001 | 14/09/1999 | 4.94 | 20/09/1999 | -4 |
FI0000000001 | 15/09/1999 | 5.1 | 20/09/1999 | -3 |
FI0000000001 | 16/09/1999 | 5.1 | 20/09/1999 | -2 |
FI0000000001 | 17/09/1999 | 5.15 | 20/09/1999 | -1 |
FI0000000001 | 20/09/1999 | 5.46 | 20/09/1999 | 0 |
FI0000000001 | 21/09/1999 | 5.43 | 20/09/1999 | 1 |
FI0000000001 | 22/09/1999 | 5.39 | 20/09/1999 | 2 |
FI0000000001 | 23/09/1999 | 5.4 | 20/09/1999 | 3 |
FI0000000001 | 24/09/1999 | 5.36 | 20/09/1999 | 4 |
FI0000000001 | 27/09/1999 | 5.3 | 20/09/1999 | 5 |
FI0000000177 | 28/02/2008 | 12.5 | 20/03/2008 | -15 |
FI0000000177 | 29/02/2008 | 12.4 | 20/03/2008 | -14 |
FI0000000177 | 3/03/2008 | 11.6 | 20/03/2008 | -13 |
FI0000000177 | 4/03/2008 | 11.1 | 20/03/2008 | -12 |
FI0000000177 | 5/03/2008 | 11.1 | 20/03/2008 | -11 |
FI0000000177 | 6/03/2008 | 11.2 | 20/03/2008 | -10 |
FI0000000177 | 7/03/2008 | 11.1 | 20/03/2008 | -9 |
FI0000000177 | 10/03/2008 | 11 | 20/03/2008 | -8 |
FI0000000177 | 11/03/2008 | 11.2 | 20/03/2008 | -7 |
FI0000000177 | 12/03/2008 | 11.4 | 20/03/2008 | -6 |
FI0000000177 | 13/03/2008 | 11.5 | 20/03/2008 | -5 |
FI0000000177 | 14/03/2008 | 11.4 | 20/03/2008 | -4 |
FI0000000177 | 17/03/2008 | 10.9 | 20/03/2008 | -3 |
FI0000000177 | 18/03/2008 | 11.5 | 20/03/2008 | -2 |
FI0000000177 | 19/03/2008 | 11.3 | 20/03/2008 | -1 |
FI0000000177 | 20/03/2008 | 16 | 20/03/2008 | 0 |
FI0000000177 | 25/03/2008 | 16 | 20/03/2008 | 1 |
FI0000000177 | 26/03/2008 | 16 | 20/03/2008 | 2 |
FI0000000177 | 27/03/2008 | 16 | 20/03/2008 | 3 |
FI0000000177 | 28/03/2008 | 15.6 | 20/03/2008 | 4 |
FI0000000177 | 31/03/2008 | 15.6 | 20/03/2008 | 5 |
I would like to do the following:
For calday 0, I would like to calculate the return based on the price on calday 0 and the first available price for a given ISIN and event day. There are many ISIN and event day in the dataset. Each ISIN could have multiple events. The first return should be (28.1-23.2)/23.2=0.21121.
Can anyone please help? Thanks in advance.
data have;
infile cards expandtabs truncover;
input isin : $20. date : ddmmyy12. price event_day : ddmmyy12. calday;
format date event_day ddmmyy10.;
cards;
FI0000000001 22/09/1997 23.2 13/10/1997 -15
FI0000000001 23/09/1997 22.7 13/10/1997 -14
FI0000000001 24/09/1997 22.5 13/10/1997 -13
FI0000000001 25/09/1997 23 13/10/1997 -12
FI0000000001 26/09/1997 24 13/10/1997 -11
FI0000000001 29/09/1997 25 13/10/1997 -10
FI0000000001 30/09/1997 25.1 13/10/1997 -9
FI0000000001 1/10/1997 24.7 13/10/1997 -8
FI0000000001 2/10/1997 25.4 13/10/1997 -7
FI0000000001 3/10/1997 26.5 13/10/1997 -6
FI0000000001 6/10/1997 27 13/10/1997 -5
FI0000000001 7/10/1997 26.7 13/10/1997 -4
FI0000000001 8/10/1997 26.7 13/10/1997 -3
FI0000000001 9/10/1997 25.1 13/10/1997 -2
FI0000000001 10/10/1997 25.2 13/10/1997 -1
FI0000000001 13/10/1997 28.1 13/10/1997 0
FI0000000001 14/10/1997 27.2 13/10/1997 1
FI0000000001 15/10/1997 26.4 13/10/1997 2
FI0000000001 16/10/1997 26.5 13/10/1997 3
FI0000000001 17/10/1997 26.3 13/10/1997 4
FI0000000001 20/10/1997 26.9 13/10/1997 5
FI0000000001 30/08/1999 5.15 20/09/1999 -15
FI0000000001 31/08/1999 5 20/09/1999 -14
FI0000000001 1/09/1999 4.99 20/09/1999 -13
FI0000000001 2/09/1999 4.93 20/09/1999 -12
FI0000000001 3/09/1999 4.97 20/09/1999 -11
FI0000000001 6/09/1999 5.04 20/09/1999 -10
FI0000000001 7/09/1999 5.17 20/09/1999 -9
FI0000000001 8/09/1999 5.03 20/09/1999 -8
FI0000000001 9/09/1999 5.03 20/09/1999 -7
FI0000000001 10/09/1999 4.98 20/09/1999 -6
FI0000000001 13/09/1999 4.98 20/09/1999 -5
FI0000000001 14/09/1999 4.94 20/09/1999 -4
FI0000000001 15/09/1999 5.1 20/09/1999 -3
FI0000000001 16/09/1999 5.1 20/09/1999 -2
FI0000000001 17/09/1999 5.15 20/09/1999 -1
FI0000000001 20/09/1999 5.46 20/09/1999 0
FI0000000001 21/09/1999 5.43 20/09/1999 1
FI0000000001 22/09/1999 5.39 20/09/1999 2
FI0000000001 23/09/1999 5.4 20/09/1999 3
FI0000000001 24/09/1999 5.36 20/09/1999 4
FI0000000001 27/09/1999 5.3 20/09/1999 5
FI0000000177 28/02/2008 12.5 20/03/2008 -15
FI0000000177 29/02/2008 12.4 20/03/2008 -14
FI0000000177 3/03/2008 11.6 20/03/2008 -13
FI0000000177 4/03/2008 11.1 20/03/2008 -12
FI0000000177 5/03/2008 11.1 20/03/2008 -11
FI0000000177 6/03/2008 11.2 20/03/2008 -10
FI0000000177 7/03/2008 11.1 20/03/2008 -9
FI0000000177 10/03/2008 11 20/03/2008 -8
FI0000000177 11/03/2008 11.2 20/03/2008 -7
FI0000000177 12/03/2008 11.4 20/03/2008 -6
FI0000000177 13/03/2008 11.5 20/03/2008 -5
FI0000000177 14/03/2008 11.4 20/03/2008 -4
FI0000000177 17/03/2008 10.9 20/03/2008 -3
FI0000000177 18/03/2008 11.5 20/03/2008 -2
FI0000000177 19/03/2008 11.3 20/03/2008 -1
FI0000000177 20/03/2008 16 20/03/2008 0
FI0000000177 25/03/2008 16 20/03/2008 1
FI0000000177 26/03/2008 16 20/03/2008 2
FI0000000177 27/03/2008 16 20/03/2008 3
FI0000000177 28/03/2008 15.6 20/03/2008 4
FI0000000177 31/03/2008 15.6 20/03/2008 5
;
run;
data want;
set have;
by isin event_day;
retain temp;
if first.event_day then temp=price;
if calday=0 then want=(price-temp)/temp;
run;
data have;
infile cards expandtabs truncover;
input isin : $20. date : ddmmyy12. price event_day : ddmmyy12. calday;
format date event_day ddmmyy10.;
cards;
FI0000000001 22/09/1997 23.2 13/10/1997 -15
FI0000000001 23/09/1997 22.7 13/10/1997 -14
FI0000000001 24/09/1997 22.5 13/10/1997 -13
FI0000000001 25/09/1997 23 13/10/1997 -12
FI0000000001 26/09/1997 24 13/10/1997 -11
FI0000000001 29/09/1997 25 13/10/1997 -10
FI0000000001 30/09/1997 25.1 13/10/1997 -9
FI0000000001 1/10/1997 24.7 13/10/1997 -8
FI0000000001 2/10/1997 25.4 13/10/1997 -7
FI0000000001 3/10/1997 26.5 13/10/1997 -6
FI0000000001 6/10/1997 27 13/10/1997 -5
FI0000000001 7/10/1997 26.7 13/10/1997 -4
FI0000000001 8/10/1997 26.7 13/10/1997 -3
FI0000000001 9/10/1997 25.1 13/10/1997 -2
FI0000000001 10/10/1997 25.2 13/10/1997 -1
FI0000000001 13/10/1997 28.1 13/10/1997 0
FI0000000001 14/10/1997 27.2 13/10/1997 1
FI0000000001 15/10/1997 26.4 13/10/1997 2
FI0000000001 16/10/1997 26.5 13/10/1997 3
FI0000000001 17/10/1997 26.3 13/10/1997 4
FI0000000001 20/10/1997 26.9 13/10/1997 5
FI0000000001 30/08/1999 5.15 20/09/1999 -15
FI0000000001 31/08/1999 5 20/09/1999 -14
FI0000000001 1/09/1999 4.99 20/09/1999 -13
FI0000000001 2/09/1999 4.93 20/09/1999 -12
FI0000000001 3/09/1999 4.97 20/09/1999 -11
FI0000000001 6/09/1999 5.04 20/09/1999 -10
FI0000000001 7/09/1999 5.17 20/09/1999 -9
FI0000000001 8/09/1999 5.03 20/09/1999 -8
FI0000000001 9/09/1999 5.03 20/09/1999 -7
FI0000000001 10/09/1999 4.98 20/09/1999 -6
FI0000000001 13/09/1999 4.98 20/09/1999 -5
FI0000000001 14/09/1999 4.94 20/09/1999 -4
FI0000000001 15/09/1999 5.1 20/09/1999 -3
FI0000000001 16/09/1999 5.1 20/09/1999 -2
FI0000000001 17/09/1999 5.15 20/09/1999 -1
FI0000000001 20/09/1999 5.46 20/09/1999 0
FI0000000001 21/09/1999 5.43 20/09/1999 1
FI0000000001 22/09/1999 5.39 20/09/1999 2
FI0000000001 23/09/1999 5.4 20/09/1999 3
FI0000000001 24/09/1999 5.36 20/09/1999 4
FI0000000001 27/09/1999 5.3 20/09/1999 5
FI0000000177 28/02/2008 12.5 20/03/2008 -15
FI0000000177 29/02/2008 12.4 20/03/2008 -14
FI0000000177 3/03/2008 11.6 20/03/2008 -13
FI0000000177 4/03/2008 11.1 20/03/2008 -12
FI0000000177 5/03/2008 11.1 20/03/2008 -11
FI0000000177 6/03/2008 11.2 20/03/2008 -10
FI0000000177 7/03/2008 11.1 20/03/2008 -9
FI0000000177 10/03/2008 11 20/03/2008 -8
FI0000000177 11/03/2008 11.2 20/03/2008 -7
FI0000000177 12/03/2008 11.4 20/03/2008 -6
FI0000000177 13/03/2008 11.5 20/03/2008 -5
FI0000000177 14/03/2008 11.4 20/03/2008 -4
FI0000000177 17/03/2008 10.9 20/03/2008 -3
FI0000000177 18/03/2008 11.5 20/03/2008 -2
FI0000000177 19/03/2008 11.3 20/03/2008 -1
FI0000000177 20/03/2008 16 20/03/2008 0
FI0000000177 25/03/2008 16 20/03/2008 1
FI0000000177 26/03/2008 16 20/03/2008 2
FI0000000177 27/03/2008 16 20/03/2008 3
FI0000000177 28/03/2008 15.6 20/03/2008 4
FI0000000177 31/03/2008 15.6 20/03/2008 5
;
run;
data want;
set have;
by isin event_day;
retain temp;
if first.event_day then temp=price;
if calday=0 then want=(price-temp)/temp;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.