BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bob021
Calcite | Level 5

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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

2 REPLIES 2
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

Thank you very much. Working like a charm. 🙂 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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