DATA Step, Macro, Functions and more

Find a difference between two variables conditional on other variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Find a difference between two variables conditional on other variables

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


Accepted Solutions
Solution
‎02-21-2017 08:00 PM
Super User
Posts: 19,822

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

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


All Replies
Occasional Contributor
Posts: 7

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

Basically, for two adjacent years, find a pair with same strike price and expiry date and record the difference
Solution
‎02-21-2017 08:00 PM
Super User
Posts: 19,822

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

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;
Occasional Contributor
Posts: 7

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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