Ho to add a serial number based on the previous observations

[Zatere]

Hi there,

I wondered if you could help me.

I have the below dataset:

data have;
input id date :ddmmyy10.;
format date ddmmyy10.;
datalines;
1 20/11/2019
1 21/11/2019
1 22/11/2019
1 25/11/2019
1 27/11/2019
1 29/11/2019
2 20/11/2019
2 21/11/2019
2 23/11/2019
2 25/11/2019
2 26/11/2019
2 28/11/2019
3 29/11/2019
3 01/12/2019
;
run;

I would like to add a column that represents the number of dates in a row.

This is what I wanted:

data want;
input id date :ddmmyy10. COLUMN_NEEDED;
format date ddmmyy10.;
datalines;
1 20/11/2019 1
1 21/11/2019 2
1 22/11/2019 3
1 25/11/2019 1
1 27/11/2019 1
1 29/11/2019 1
2 20/11/2019 1
2 21/11/2019 2
2 23/11/2019 1
2 25/11/2019 1
2 26/11/2019 2
2 28/11/2019 1
3 29/11/2019 1
3 01/12/2019 1
;
run;

As long as there are dates in a row by each ID, the COLUMN_NEEDED adds 1.

Thanks in advance.

[sbxkoenk · Accepted solution]

Like this?

data want(drop=a);
 set have;
 retain column_needed;
 by id;
 a=lag(date);
 if first.id then column_needed=1;
 else do;
  if INTCK('day',a,date)=1 then column_needed=column_needed+1;
  else column_needed=1;
      end;
run;
/* end of program */

Koen

[Amir]

Hi,

Thank you for supplying the data in the form of data steps.

I added a couple of extra rows to your have data set for testing:

/* added 2 extra rows at end to test consecutive dates */
/* that spread over different id values are handled OK */
data have;
input id date :ddmmyy10.;
format date ddmmyy10.;
datalines;
1 20/11/2019
1 21/11/2019
1 22/11/2019
1 25/11/2019
1 27/11/2019
1 29/11/2019
2 20/11/2019
2 21/11/2019
2 23/11/2019
2 25/11/2019
2 26/11/2019
2 28/11/2019
3 29/11/2019
3 01/12/2019
3 02/12/2019
4 03/12/2019
;

/* use DOW loop to process by id */
data want2(drop = last_date);
  do until (last.id);
    set have;
    
    by id;
    
    if sum(date, -last_date) = 1 then
      column_needed + 1;
    else
      column_needed = 1;
      
    last_date = date;
    output;
  end;
run;

The above gives the following output:

Thanks & kind regards,

Amir.