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
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;
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;
Thank you very much. Working like a charm. 🙂
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.
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.