topic Re: How to conditionally recode variables based on Gap between dates in SAS Programming

How to conditionally recode variables based on Gap between dates

shasank — Thu, 28 Dec 2017 02:25:00 GMT

Hi SAS Community,

I need in your help in the below regard.

I have a data set like this:-

ID	StartDate
1	9-May-12
1	10-May-12
1	10-May-12
1	30-May-12
1	30-Jun-12

2	17-May-12
2	30-May-12
2	31-May-12
2	26-Jun-12

3	26-Jun-12
3	29-Jun-12
3	27-Jul-12
3	25-Aug-12

And want an output like this:-

ID	StartDate	Line
1	9-May-12	1
1	10-May-12	1
1	10-May-12	1
1	30-May-12	2

2	17-May-12	1
2	30-May-12	1
2	31-May-12	1
2	26-Jun-12	2

3	26-Jun-12	1
3	29-Jun-12	1
3	27-Jul-12	2
3	25-Aug-12	3

Basically, The logic is that if there is a gap of more than 20 between the dates by ID then the Line variable should increase by 1.

Thank you for your time and I appreciate your help.

Regards,

SC.

Re: How to conditionally recode variables based on Gap between dates

novinosrin — Thu, 28 Dec 2017 02:25:22 GMT

data have;
input ID StartDate :date7.;
format startdate date7.;
datalines;
1 9-May-12
1 10-May-12
1 10-May-12
1 30-May-12
2 17-May-12
2 30-May-12
2 31-May-12
2 26-Jun-12
3 26-Jun-12
3 29-Jun-12
3 27-Jul-12
3 25-Aug-12
;

data want;
set have;
by id;
k=lag(startdate);
if first.id then line=1;
else if intck('day',k,startdate)>=20 then line+1;
drop k;
run;

Re: How to conditionally recode variables based on Gap between dates

shasank — Thu, 28 Dec 2017 02:29:27 GMT

You are of great help. Thank you very much.