topic Re: Find a difference between two variables conditional on other variables in SAS Programming

Find a difference between two variables conditional on other variables

Sangho — Tue, 21 Feb 2017 23:36:37 GMT

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!!

Re: Find a difference between two variables conditional on other variables

Sangho — Tue, 21 Feb 2017 23:41:13 GMT

Basically, for two adjacent years, find a pair with same strike price and expiry date and record the difference

Re: Find a difference between two variables conditional on other variables

Reeza — Tue, 21 Feb 2017 23:45:51 GMT

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;

Re: Find a difference between two variables conditional on other variables

Sangho — Wed, 22 Feb 2017 01:02:45 GMT

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;