BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sangho
Obsidian | Level 7

There is a stock option data with year, number of shares, strike price and expiry date.Capture1.PNG

with a code 

 

data option;
input year number strike_price expiry_date;
datalines;
2010 100 8 20120101
2010 200 9 20130101
2010 250 6 20110101
2009 150 8 20120101
2009 250 9 20140101
2008 250 8 20120101
2008 450 9 20140101
;
run;

I want to find out whether there is a yearly change in number of shares only for options with the same strike price and expiry date.

 

For example, observation 1 and 4, they have the same strike price and expiry date, so I record -50 for 2010. But observation 2 and 5, they have the same strike price but different expiry date, so I don't record anything. Eventually, I want something like below.Capture2.PNG

This was just an example of one firm with 3 years, but in the dataset I have, there are multiple firms with multiple years.

Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

It's probably best to expand your sample data so that we can account for the multiple firms/years from the start.

 

This is probably one case, where it's worth transposing your data to a wide format and then summarizing. 

Or using DIF() function. This could be expanded by adding the Company to the BY group in the sort and data step.

 

data option;
	input year number strike_price expiry_date;
	datalines;
2010 100 8 20120101
2010 200 9 20130101
2010 250 6 20110101
2009 150 8 20120101
2009 250 9 20140101
2008 250 8 20120101
2008 450 9 20140101
;
run;

proc sort data=option;
	by strike_price expiry_date year;
run;

data want;
	set option;
	by strike_price expiry_date;
	Change = dif(number);

	if first.expiry_date then
		change=0;
run;

View solution in original post

3 REPLIES 3
Sangho
Obsidian | Level 7
Basically, for two adjacent years, find a pair with same strike price and expiry date and record the difference
Reeza
Super User

It's probably best to expand your sample data so that we can account for the multiple firms/years from the start.

 

This is probably one case, where it's worth transposing your data to a wide format and then summarizing. 

Or using DIF() function. This could be expanded by adding the Company to the BY group in the sort and data step.

 

data option;
	input year number strike_price expiry_date;
	datalines;
2010 100 8 20120101
2010 200 9 20130101
2010 250 6 20110101
2009 150 8 20120101
2009 250 9 20140101
2008 250 8 20120101
2008 450 9 20140101
;
run;

proc sort data=option;
	by strike_price expiry_date year;
run;

data want;
	set option;
	by strike_price expiry_date;
	Change = dif(number);

	if first.expiry_date then
		change=0;
run;
Sangho
Obsidian | Level 7
Thank you so much. It works great.
For my case with multiple firms, I added firm identification variable in front of strike_price variable.
proc sort data=option;
by company strike_price expiry_date year;
run;

data want;
set option;
by company strike_price expiry_date;
Change = dif(number);

if first.expiry_date then
change=0;
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4404 views
  • 1 like
  • 2 in conversation