New column based on count of days between two date in existing column

[bob021]

Hi to all

This is my table with 3 columns.

id serial date

1 334 17.03.2015

2 334 18.03.2015

3 334 15.07.2015

4 334 19.09.2015

5 334 03.10.2015

1 335 10.02.2015

2 335 13.02.2015

3 335 18.03.2015

4 335 13.08.2015

5 335 05.10.2015

I want to get this: (new column [control] based on count of days between two date)

id serial date control

1 334 17.03.2015 OK

2 334 18.03.2015 

3 334 15.07.2015 OK

4 334 25.07.2015

5 334 03.10.2015 OK

1 335 10.02.2015 OK

2 335 13.02.2015  

3 335 18.02.2015 

4 335 13.08.2015 OK

5 335 05.10.2015 OK

Rules and examples:

1. Serial with first (oldest date) or ID 1 is always control = 'OK'

2. Count days beetwen first and second date for serial 334 and if less then 14 days then live empty cell, if not then OK

Continue until days count > 14, then start new count

 example: (id 1 serial 334) 17.03.2015 - (id 2 serial 334) 18.03.2015 < 14  day so  control = ' '

3. Count days beetwen first and third date for serial 334

example: ((id 1 serial 334) 17.03.2015 - (id 3 serial 334) 15.07.2015 > 14 days so   control = ' OK'

4. After reaching OK start to count fom that position. 

example: ((id 3 serial 334) 15.07.2015 - (id 4 serial 334) 25.07.2015 < 14 days so   control = ' '

5. Next:

example:  ((id 3 serial 334) 15.07.2015 - (id 5 serial 334) 03.10.2015 > 14 days so   control = ' OK'

Stop and go to the next serial

Same for any other serial in table.

Thank you in advance

Bob

[Steelers_In_DC · Accepted solution]

Here is a solution, but notice that your input and output are not the same:

data have;
infile cards dsd;
informat date ddmmyy10.;
format date ddmmyy10.;
input id serial date;
cards;
1,334,17.03.2015
2,334,18.03.2015
3,334,15.07.2015
4,334,19.09.2015
5,334,03.10.2015
1,335,10.02.2015
2,335,13.02.2015
3,335,18.03.2015
4,335,13.08.2015
5,335,05.10.2015
;

proc sort data=have;by serial date id;

data want;
set have;
by serial date id notsorted;
if first.serial then Control = 'OK';
if date-lag(date) > 14 then control = 'OK';
run;

[bob021]

Thank you very much. Working like a charm. 🙂