That's very much true and I was overthinking about those complicate ways. But it works well if I don't call if they occur in consecutive order (such as 2nd record vs.1st, and 3rd record vs. 2nd). What if I care (in some cases I do need to)? ... View more

Thanks so much. You guys are really clever and I really suck at processing rectangle data. Can I ask another related question? For a similar data with with each ID with multiple admission dates (admdate) and within each date, there are test dates (testdate)and a group variable (GROUP) and Score variable with each test. I'm hoping to get the following: W/n each person and same admission, if consecutive records have the same group vairalbes, I'd like to sum up the conse first assessment's RUG value is the same as the next record's value, then I want to sum up the score values and drop the one of the records -- so I don't care about the test date, only about ID, admdate, group and score. I know the lag function will allow me to compare values, but how do I delete the records? I was only able to come up with the following, but how do I delete the previous record (w/n the same ID and admission date) that has the same group? Would using retain work? How? Sorry, I'm really sucked at reading rectangle data, lag function and retain. So thanks for being patient with me. The data is below if it helps. Thanks very much. proc sort data=mydata.testdata out=test; by id admdate testdate;  run; data want; set test; by ID admdate testdate; prev_group= lag(group); prev_score=lag(score); if first.testdate and prev_group = group then score2=sum(score,prev_score); if first.testdate and prev_group ne group then score2=score; ID Admdate Testdate Group Score 1 30/04/2001 01-Apr-06 SE2 49 1 30/04/2001 14-May-06 RLB 17 2 16/12/2004 01-Apr-06 SSB 80 2 16/12/2004 30-Jun-06 SSB 82 2 16/12/2004 30-Sep-06 SE2 104 2 16/12/2004 30-Dec-06 SSB 80 2 16/12/2004 30-Mar-07 CB2 2 3 24/08/2005 01-Apr-06 RLB 59 4 08/07/1989 01-Apr-06 CC2 88 4 08/07/1989 01-Jul-06 CC2 71 5 15/04/2000 01-Apr-06 CB1 8 5 15/04/2000 12-Apr-06 CB1 19 6 10/04/2001 01-Apr-06 SSB 69 6 10/04/2001 17-Jun-06 SSB 27 7 11/04/2005 01-Apr-06 CB1 17 7 11/04/2005 24-Apr-06 SSA 78 7 11/04/2005 24-Jul-06 SSA 14 8 07/04/2005 01-Apr-06 PB1 8 8 07/04/2005 20-Apr-06 CA1 45 ... View more

Thanks for the great advice from everyone. I've a maybe not so related question and I thought I would just ask here instead of creating a new subject entry. I want to compare data between records by ID, admission date and a group variable. If Group is the same between two consecutive lines (w/n the same admission), then combine the record and output the appropriate dates so that I can calculate the time elapse between dates. data test; input pid $ admission_date1 $ assessment_date group $; cards; 1      1/1/2011    1/3/2011   A     1      1/1/2011    1/9/2011   B     1      3/4/2011    4/2/2011   B    1      3/4/2011    4/8/2011   B    2      6/1/2010    6/4/2011   C    2      6/5/2010    7/4/2011   C    3      7/1/2011    7/9/2011   D   run; *Desired output: PID  Adm date  Date1        Date2  Group  1      1/1/2011    1/1/2011  1/3/2011    A               *admission date repeating for each record, the first record's date1=admission date 1      1/1/2011    1/3/2011  1/9/2011    B               *In the subsequent record, date1=date from the previous record 1      3/4/2011    3/4/2011  4/8/2011    B               *Group D is the same between records w/n the same admission, so output as a single record and appr. date 2      6/1/2010    6/12010   6/4/2011    C         2      6/5/2010    6/5/2010  7/4/2011    C          3      7/1/2011    7/1/2011  7/9/2011    D Hope the above makes sense. (The real data is more complicate). And thanks for whatever help you can give. ... View more

Thanks so much. (I liked the actual code in reply). I'm also confused now. For example, I've seen examples end with "if last.var then output" (like you did here) while many don't. When do I need it? Thanks. ... View more

It indeed worked now. Thanks much though I still don't fullly understand the logic of frist and last. Any gook book chapters or websites on the use of first, last, retain, accumulating, summing? (For example, why do you only need to specify the second variable in the BY list). Thanks. data want;  set original; by pid date; if first.pid and first.date then seq_id=1; else seq_id+1; run; ... View more