Solved
New Contributor
Posts: 2

# How to do a subtraction between certain rows

Could somebody help me to deal with this problem, thanks so much.

Basicly, I had thousands of PIN, dates, and mark (which I pasted below). I need to subtract dates between first "new start <400" till last "keep start <400". For example, for PIN=100, I subtract last "keep start <400" 1/7/2010 - first "new start <400" 2/25/2009.

Then, I will need another subtraction for the same pin between "keep start <400" 11/24/2015-  "new start <400" 3/2/2011.

 PIN DATE_COLLECTED mark 100 2/25/2009 new START <400 100 6/8/2009 keep START  <400 100 1/7/2010 keep START  <400 100 3/2/2011 new START  <400 100 4/26/2011 keep START  <400 100 6/29/2011 keep START  <400 100 12/14/2011 keep START  <400 100 6/6/2012 keep START  <400 100 12/12/2012 keep START  <400 100 6/12/2013 keep START  <400 100 12/11/2013 keep START  <400 100 4/18/2014 keep START  <400 100 10/7/2014 keep START  <400 100 12/16/2014 keep START  <400 100 3/13/2015 keep START  <400 100 11/24/2015 keep START  <400

Any suggestion would be appreciated.

Accepted Solutions
Solution
‎09-06-2017 09:48 AM
Super User
Posts: 6,632

## Re: How to do a subtraction between certain rows

Try:

data want;

set have;

by PIN mark notsorted;

if first.PIN then baseline = . ;

retain baseline;

if mark = "new start <400" then baseline = date;

if last.mark and mark = "keep START  <400" then difference = date - baseline;

drop baseline;

run;

All Replies
Solution
‎09-06-2017 09:48 AM
Super User
Posts: 6,632

## Re: How to do a subtraction between certain rows

Try:

data want;

set have;

by PIN mark notsorted;

if first.PIN then baseline = . ;

retain baseline;

if mark = "new start <400" then baseline = date;

if last.mark and mark = "keep START  <400" then difference = date - baseline;

drop baseline;

run;

New Contributor
Posts: 2