Calculate the time difference between rows

[sandyzman1]

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

[mkeintz]

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?

[sandyzman1]

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.

[Ksharp · Accepted solution]

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;

[sandyzman1]

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