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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.