topic Re: Subtracting Observations within a Group in SAS Procedures

Subtracting Observations within a Group

dereck255 — Sun, 08 Mar 2015 14:11:54 GMT

Hello,

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

SubjectNum	DateVisit
1	01Jan2015
1	01Feb2015
1	11Feb2015
2	01Dec2014
2	12Dec2014
2	01Jan2015
2	07Jan2015

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!

Re: Subtracting Observations within a Group

slchen — Sun, 08 Mar 2015 15:32:01 GMT

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;

Re: Subtracting Observations within a Group

Astounding — Sun, 08 Mar 2015 15:45:14 GMT

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;

Re: Subtracting Observations within a Group

naveen_srini — Sun, 08 Mar 2015 18:38:42 GMT

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;

Re: Subtracting Observations within a Group

dereck255 — Sun, 08 Mar 2015 21:09:01 GMT

Thank you for your help! It worked out perfectly!