Solved
Contributor
Posts: 25

# Subtracting Observations within a Group

Hello,

In my data set, which is an Excel input, I have a list of dates from patient visits. See example below:

SubjectNumDateVisit

1

01Jan2015
101Feb2015
111Feb2015
201Dec2014
212Dec2014
201Jan2015
207Jan2015

I'm trying to find the number of days between each visit for each subject. When I use the following code, SAS substracts the DateVisit observations regardless of Subject Number:

data xyz;

set abc;

DaysBtwn=dif(DateVisit);

run;

I tried adding a by (by=SubjectNum) statement, and that didn't work. Not sure if a by statement would do anything here anyway. What's a good (and easy) way to get this done? Thank you!

Accepted Solutions
Solution
‎03-08-2015 02:38 PM
Frequent Contributor
Posts: 115

## Re: Subtracting Observations within a Group

I suggest Use @Astounding expert help as I copy many of his codes too, but if you are novice like me, just play:

data have;

input subjectnum \$ datevisit :date9.;

format datevisit date9.;

datalines;

1 01Jan2015

1 01Feb2015

1 11Feb2015

2 01Dec2014

2 12Dec2014

2 01Jan2015

2 07Jan2015

;

data want;

set have;

by subjectnum notsorted;

k=lag(datevisit);

if not first.subjectnum then DaysBtwn=intck('day',k,datevisit);

drop k;

run;

All Replies
Super Contributor
Posts: 275

## Re: Subtracting Observations within a Group

data xyz;

set abc;

by SubjectNum

retain LastVisit;

LastVisit=lag(DateVisit)

if first.SubjectNum then Diff=.;

else DaysBtwn=DateVisit-LastVisit;

drop LastVisit;

run;

Or:

data xyz;

set abc;

if _n_^=1 then set abc (keep=SubjectNum DateVisit rename=(SubjectNum=_SubjectNum DateVisit=_DateVisit));

Diff=.;

if SubjectNum=_SubjectNum and not first.SubjectNum then Diff=_DateVisit-DateVisit;

drop _SubjectNum _DateVisit;

run;

Super User
Posts: 6,789

## Re: Subtracting Observations within a Group

While earlier suggestions might work, this would be a little more similar to your original attempts:

data xyz;

set abc;

by SubjectNum;

DaysBtwn = diff(DateVisit);

if first.SubjectNum then DaysBtwn=.;

run;

Note that DIFF (as well as LAG) must execute on every observation to get the right answer.  Well, at least 99% of the time anyway.  So never do this:

data xyz;

set abc;

by SubjectNum;

if first.SubjectNum=0 then DaysBtwn = diff(DateVisit);

run;

Solution
‎03-08-2015 02:38 PM
Frequent Contributor
Posts: 115

## Re: Subtracting Observations within a Group

I suggest Use @Astounding expert help as I copy many of his codes too, but if you are novice like me, just play:

data have;

input subjectnum \$ datevisit :date9.;

format datevisit date9.;

datalines;

1 01Jan2015

1 01Feb2015

1 11Feb2015

2 01Dec2014

2 12Dec2014

2 01Jan2015

2 07Jan2015

;

data want;

set have;

by subjectnum notsorted;

k=lag(datevisit);

if not first.subjectnum then DaysBtwn=intck('day',k,datevisit);

drop k;

run;

Contributor
Posts: 25