topic How to calculate return based on the first available price for a given isin and event day? in SAS Programming

How to calculate return based on the first available price for a given isin and event day?

bd_user_10 — Thu, 24 Jan 2019 12:11:30 GMT

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.

Re: How to calculate return based on the first available price for a given isin and event day?

Ksharp — Thu, 24 Jan 2019 12:51:44 GMT

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;

Re: How to calculate return based on the first available price for a given isin and event day?

bd_user_10 — Thu, 24 Jan 2019 21:00:36 GMT

Thanks for the code. It works perfectly!