cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bd_user_10
Quartz | Level 8 bd_user_10
Quartz | Level 8
Go to Solution

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

Posted 01-24-2019 07:11 AM (1220 views)

Hi Everyone,

 

I have the following dataset:

 

isindatepriceevent_daycalday
FI000000000122/09/199723.213/10/1997-15
FI000000000123/09/199722.713/10/1997-14
FI000000000124/09/199722.513/10/1997-13
FI000000000125/09/19972313/10/1997-12
FI000000000126/09/19972413/10/1997-11
FI000000000129/09/19972513/10/1997-10
FI000000000130/09/199725.113/10/1997-9
FI00000000011/10/199724.713/10/1997-8
FI00000000012/10/199725.413/10/1997-7
FI00000000013/10/199726.513/10/1997-6
FI00000000016/10/19972713/10/1997-5
FI00000000017/10/199726.713/10/1997-4
FI00000000018/10/199726.713/10/1997-3
FI00000000019/10/199725.113/10/1997-2
FI000000000110/10/199725.213/10/1997-1
FI000000000113/10/199728.113/10/19970
FI000000000114/10/199727.213/10/19971
FI000000000115/10/199726.413/10/19972
FI000000000116/10/199726.513/10/19973
FI000000000117/10/199726.313/10/19974
FI000000000120/10/199726.913/10/19975
FI000000000130/08/19995.1520/09/1999-15
FI000000000131/08/1999520/09/1999-14
FI00000000011/09/19994.9920/09/1999-13
FI00000000012/09/19994.9320/09/1999-12
FI00000000013/09/19994.9720/09/1999-11
FI00000000016/09/19995.0420/09/1999-10
FI00000000017/09/19995.1720/09/1999-9
FI00000000018/09/19995.0320/09/1999-8
FI00000000019/09/19995.0320/09/1999-7
FI000000000110/09/19994.9820/09/1999-6
FI000000000113/09/19994.9820/09/1999-5
FI000000000114/09/19994.9420/09/1999-4
FI000000000115/09/19995.120/09/1999-3
FI000000000116/09/19995.120/09/1999-2
FI000000000117/09/19995.1520/09/1999-1
FI000000000120/09/19995.4620/09/19990
FI000000000121/09/19995.4320/09/19991
FI000000000122/09/19995.3920/09/19992
FI000000000123/09/19995.420/09/19993
FI000000000124/09/19995.3620/09/19994
FI000000000127/09/19995.320/09/19995
FI000000017728/02/200812.520/03/2008-15
FI000000017729/02/200812.420/03/2008-14
FI00000001773/03/200811.620/03/2008-13
FI00000001774/03/200811.120/03/2008-12
FI00000001775/03/200811.120/03/2008-11
FI00000001776/03/200811.220/03/2008-10
FI00000001777/03/200811.120/03/2008-9
FI000000017710/03/20081120/03/2008-8
FI000000017711/03/200811.220/03/2008-7
FI000000017712/03/200811.420/03/2008-6
FI000000017713/03/200811.520/03/2008-5
FI000000017714/03/200811.420/03/2008-4
FI000000017717/03/200810.920/03/2008-3
FI000000017718/03/200811.520/03/2008-2
FI000000017719/03/200811.320/03/2008-1
FI000000017720/03/20081620/03/20080
FI000000017725/03/20081620/03/20081
FI000000017726/03/20081620/03/20082
FI000000017727/03/20081620/03/20083
FI000000017728/03/200815.620/03/20084
FI000000017731/03/200815.620/03/20085

 

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.

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User Ksharp
Super User

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

Posted 01-24-2019 07:51 AM (1212 views)  |   In reply to bd_user_10 
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;

View solution in original post

0 Likes
2 REPLIES 2
Ksharp
Super User Ksharp
Super User

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

Posted 01-24-2019 07:51 AM (1213 views)  |   In reply to bd_user_10 
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;
0 Likes
bd_user_10
Quartz | Level 8 bd_user_10
Quartz | Level 8

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

Posted 01-24-2019 04:00 PM (1184 views)  |   In reply to Ksharp
Thanks for the code. It works perfectly!
0 Likes

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • ‎01-24-2019 07:11 AM
  • 1221 views
  • 0 likes
  • 2 in conversation