## Find a difference between two variables conditional on other variables

Solved
Occasional Contributor
Posts: 15

# 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.

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

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

## 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;``````

All Replies
Occasional Contributor
Posts: 15

## 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: 23,232

## 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: 15

## 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.