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 (936 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 (928 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 (929 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 (900 views)  |   In reply to Ksharp
Thanks for the code. It works perfectly!
0 Likes

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 937 views
  • 0 likes
  • 2 in conversation