topic Re: Calculate the time difference between rows in SAS Programming

Calculate the time difference between rows

sandyzman1 — Sun, 02 May 2021 01:47:27 GMT

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

Re: Calculate the time difference between rows

mkeintz — Sun, 02 May 2021 03:19:05 GMT

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?

Re: Calculate the time difference between rows

sandyzman1 — Sun, 02 May 2021 12:24:20 GMT

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.

Re: Calculate the time difference between rows

Ksharp — Sun, 02 May 2021 12:51:37 GMT

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;

Re: Calculate the time difference between rows

sandyzman1 — Sun, 02 May 2021 19:08:04 GMT

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