There is a stock option data with year, number of shares, strike price and expiry date.
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.
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!!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.