DATA Step, Macro, Functions and more

Difference between dates across rows

Accepted Solution Solved
Reply
Super Contributor
Posts: 365
Accepted Solution

Difference between dates across rows

[ Edited ]

Hi guys, I thought below would manage calculating the difference between time periods across each rows. But I wind up with not only huge but negative numbers. Please help correcting this syntax out. What I want is about 132 days difference between two rows (07022002 and 11142002)  in the second row instead 4120000 and so forth so on for the rest of the rows.  

Thanks a lot in advance.

 

wrong output.png

data diff_date;
input Date_Visit $ ID $;
cards;
07022002 83
11142002 83
05152003 83
11042003 83
05042004 83
11102004 83
05032005 83
11012005 83
06062006 83
;

data b; set diff_date;  
/*format date_visit 9.;*/
by child_id;
retain r_date;
r_date=lag(date_visit);
if not first.child_id then do;
diff_visit=date_visit-r_date;
end;
drop r_:;
run; 

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 6,781

Re: Difference between dates across rows

It would take a few changes here and there, starting with how you read in the data.

 

data diff_date;
input Date_Visit mmddyy8.  ID $;
cards;
07022002 83
11142002 83
05152003 83
11042003 83
05042004 83
11102004 83
05032005 83
11012005 83
06062006 83
;

data want; 
set diff_date;  
by id;
diff_visit = dif(date_visit);
if first.id then diff_visit = .;
run; 

View solution in original post


All Replies
Super User
Posts: 10,270

Re: Difference between dates across rows

11142002 is not a date, but a 8-digit number (by now - more than 300 posts here - you should know that). Store dates as SAS date values, and use intck() to get distances in time.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
2 weeks ago
Super User
Posts: 6,781

Re: Difference between dates across rows

It would take a few changes here and there, starting with how you read in the data.

 

data diff_date;
input Date_Visit mmddyy8.  ID $;
cards;
07022002 83
11142002 83
05152003 83
11042003 83
05042004 83
11102004 83
05032005 83
11012005 83
06062006 83
;

data want; 
set diff_date;  
by id;
diff_visit = dif(date_visit);
if first.id then diff_visit = .;
run; 
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 98 views
  • 0 likes
  • 3 in conversation