Obsidian | Level 7

## Calculate the time difference between rows

Hi All,

I am trying to extract the BMI value before and after the surgery for following dataset:

data diff_date;
input Date_Visit \$ ID \$ surgery \$ BMI ;
cards;
07022002 1 0 40
11142002 1 0 35
05152003 1 1 35
11042003 1 0 25
05042004 2 0 50
11102004 2 0 53
05032005 2 1 45
05032006 2 0 32
05032007 2 0 25
11012005 3 0 30
06062006 3 1 25
07032006 3 0 18
;
run;

I wanted to extract the BMI value 6 months before the surgery, on the day of surgery, and 6 months after the surgery. I would really appreciate any thought. Thanks.

Best,
Sandyzman1

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate the time difference between rows

Plz post the output you want to see .

`````` data diff_date;
input Date_Visit : mmddyy10. ID  surgery  BMI ;
format Date_Visit  mmddyy10.;
cards;
07022002 1 0 40
11142002 1 0 35
05152003 1 1 35
11042003 1 0 25
05042004 2 0 50
11102004 2 0 53
05032005 2 1 45
05032006 2 0 32
05032007 2 0 25
11012005 3 0 30
06062006 3 1 25
07032006 3 0 18
;
run;
data want;
merge diff_date diff_date(keep=id date_visit surgery
rename=(date_visit=_date_visit surgery=_surgery) where=(_surgery=1));
by id;
if date_visit<intnx('month',_date_visit,-6,'s') or
date_visit>intnx('month',_date_visit,6,'s') ;
drop _: ;
run;``````
4 REPLIES 4
PROC Star

## Re: Calculate the time difference between rows

First read your visit dates as date values, not as character variables.  I.e. use the mmddyy8. informat to get sas to translate the 8 digits into the number of days after 01jan1960.

Also assign a date format (such as date9.) so when SAS displays this value, you can see it as a calendar date.

``input Date_Visit mmddyy8. ID \$ surgery \$ BMI ;format date_visit date9. ;``

Do you want the dates closest to 6 mo prior and 6 mo after, or do you want (a) the most recent date at least 6 months prior, and (b) the earliest following date at least 6 months after?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Obsidian | Level 7

## Re: Calculate the time difference between rows

Hi mkeintz,

Thanks for the suggestion. I'll make sure that I make those edits. For this analysis, I want the dates closest to 6 mo prior and 6 mo after.

Super User

## Re: Calculate the time difference between rows

Plz post the output you want to see .

`````` data diff_date;
input Date_Visit : mmddyy10. ID  surgery  BMI ;
format Date_Visit  mmddyy10.;
cards;
07022002 1 0 40
11142002 1 0 35
05152003 1 1 35
11042003 1 0 25
05042004 2 0 50
11102004 2 0 53
05032005 2 1 45
05032006 2 0 32
05032007 2 0 25
11012005 3 0 30
06062006 3 1 25
07032006 3 0 18
;
run;
data want;
merge diff_date diff_date(keep=id date_visit surgery
rename=(date_visit=_date_visit surgery=_surgery) where=(_surgery=1));
by id;
if date_visit<intnx('month',_date_visit,-6,'s') or
date_visit>intnx('month',_date_visit,6,'s') ;
drop _: ;
run;``````
Obsidian | Level 7

## Re: Calculate the time difference between rows

Hi KSharp,

Thanks. It worked. I wanted to get the date and BMI value 6 months before and after the surgery date to see how the bodyweight changed due to surgery. I added a few extra steps and got my answer. You are awesome. Thanks again.

Best,
sandyzman1

Discussion stats
• 4 replies
• 1058 views
• 0 likes
• 3 in conversation