I have a long dataset that includes multiple records per participant. Each person has an overall date range (begin_date and end_date) for trips they have made to conduct business for their job. Additionally, each person has a date1 and a dot1 that indicated shorter duration trips that fall within their overall begin_date and end_date. What I need is a new variable that gives a trip number for each row of the data. So for example, in the small dataset below for participant 1, row 1 would be trip 1, so my new variable trip, would be equal to 1, rows 2-5 are trip 2 and there were 4 shorter trips associated with trip 2, then rows 6-9 are trip 3 and they were 4 shorter trips associated with the overall date range. You can tell if the date1 and dot1 dates are all within a larger trip if the dates fall within begin_date and end_date. So basically I want to add a variable called trip and for the example row 1 would be 1, rows 2-5 would be 2, and rows 6-9 would be 3. I tried using the following code but it just gives me a count of all the rows essentially. I think the code doesn't work because I have repeated measures and it is hard to distinguish between dates but I was hoping to be able to do this without transforming the data. Any help would be greatly appreciated. data want; set have; trip+1; by begin_date; if first.begin_date then trip=1; else if date1>end_date and dot1<=end_date then trip=trip+1; run; ID begin_date end_date date1 dot1 1 02/12/06 04/29/06 02/12/06 04/29/16 1 03/14/07 01/15/08 03/14/07 04/18/07 1 03/14/07 01/15/08 04/18/07 05/16/07 1 03/14/07 01/15/08 05/16/07 09/02/07 1 03/14/07 01/15/08 09/02/07 01//15/08 1 05/15/10 08/05/12 06/26/10 08/15/10 1 05/15/10 08/05/12 01/20/11 01/25/11 1 05/15/10 08/05/12 02/01/11 08/01/11 1 05/15/10 08/05/12 04/01/12 04/03/12 ... View more

I have a dataset where participants have up to 4 visits. I need to select their most recent visit for multiple variables that does not include missing values. I know I can use last.studyid to select their most recent observation but is there a way for multiple variables to select their most recent observation for each variable that does not include missing values? A short example of what the data might look like it below. Bascially for var1 I would want the third visit value for var1 and the fourth visit value for var2 for study ID 1, for study id 2 I would want the third visit value for both, and for study ID 3 I would want the fourth visit value for var1 and the third visit value for var2. Any help would be great appreciated. ID var1 var2  visit 1   1     0     1 1   1     0     2 1   1     0     3 1   .      0    4 2   .      0    1 2  1      0    2 2  1      1    3 3  0      1    1 3  0      1    2 3  0      1    3 3  1      .    4 ... View more

I have data from 5 study visits that assessed health behaviors. The same questions were asked at each visit and I would like to determine how many unique participants reported one behavior. I have the data sets appended in long format, so I can run a proc freq and get that 110 participants reported the behavior across all visits but I want to know how many of those are unique participants versus the same participants reporting the behavior at each visit. Ideally I would like to know how many participants reported the behaviors at all visits, how many at any visit, and how many reported 1, 2, 3, and 4 visits. I previously asked a similar question but was not able to figure this out. Any suggestions would be greatly appreciated. ... View more