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. 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.