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

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

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

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

 

 

 


Accepted Solutions
Solution
‎10-13-2015 09:55 AM
Valued Guide
Posts: 854

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

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;

View solution in original post


All Replies
Solution
‎10-13-2015 09:55 AM
Valued Guide
Posts: 854

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

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;

Contributor
Posts: 60

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

Thank you very much. Working like a charm. Smiley Happy 

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 241 views
  • 1 like
  • 2 in conversation