How to create dates from other observations

[RTN]

Hi,

I am new at working with SAS 9.4. I am having problems creating dates based on other observations.

I have people with an ID, group, start_date, end_of_study. People can go from one group to another.  The start date describes the start date the time in that group. The end_of_study is the date the leave the study completely. I would like to create a new variable "end_date" that describes the date of the end of the time period in that group. For the last observation per person the end_date=end_of_study, however I am missing the end dates for the individuals with more than one observation. The end date is the day before the start date of the following observation if it's the same person. Can anyone help me with that?

data have;
input ID group start_date end_of_study ;

1 1 05-11-2002 01-01-2015 
1 3 01-01-2014 01-01-2015

2 5 01-01-2000 31-12-2015
2 4 01-01-2002 31-12-2015
2 5 01-01-2014 31-12-2015 

3 1 01-01-2000 27-07-2015
3 2 01-01-2008 27-07-2015
3 1 01-01-2010 27-07-2015
3 2 01-01-2011 27-07-2015;
run;

/*What I want*/
data want;
input ID group start_date end_date ;
1 1 05-11-2002 31-13-2013 
1 3 01-01-2014 01-01-2015

2 5 01-01-2000 31-12-2001
2 4 01-01-2002 31-12-2014
2 5 01-01-2014 31-12-2015 

3 1 01-01-2000 31-12-2007
3 2 01-01-2008 31-12-2009
3 1 01-01-2010 31-12-2010
3 2 01-01-2011 31-12-2015;
run;

Thank you in advance.

Regards,

RTN

[RTN]

I am new at working with SAS 9.4. I am having problems creating dates based on other observations.

I have people with an ID, group, start_date, end_of_study. People can go from one group to another.  The start date describes the start date the time in that group. The end_of_study is the date the leave the study completely. I would like to create a new variable "end_date" that describes the date of the end of the time period in that group. For the last observation per person the end_date=end_of_study, however I am missing the end dates for the individuals with more than one observation. The end date is the day before the start date of the following observation if it's the same person. Can anyone help me with that?

data have;
input ID group start_date end_of_study ;

1 1 05-11-2002 01-01-2015 
1 3 01-01-2014 01-01-2015

2 5 01-01-2000 31-12-2015
2 4 01-01-2002 31-12-2015
2 5 01-01-2014 31-12-2015 

3 1 01-01-2000 27-07-2015
3 2 01-01-2008 27-07-2015
3 1 01-01-2010 27-07-2015
3 2 01-01-2011 27-07-2015;
run;

/*What I want*/
data want;
input ID group start_date end_date ;
1 1 05-11-2002 31-13-2013 
1 3 01-01-2014 01-01-2015

2 5 01-01-2000 31-12-2001
2 4 01-01-2002 31-12-2014
2 5 01-01-2014 31-12-2015 

3 1 01-01-2000 31-12-2007
3 2 01-01-2008 31-12-2009
3 1 01-01-2010 31-12-2010
3 2 01-01-2011 31-12-2015;
run;

Thank you in advance.

Regards,

RTN

[SuryaKiran]

Hello,

You have to find the leading value for your start date. There is a function for finding lagging value lag(), but not for leading in SAS. Since SAS reads observation one row at a time. You can't go to next record until the previous one is processed. So you may have to merge data by itself to find leading values. 

Something like:

data have;
format start_date end_of_study ddmmyy10.;
input ID:8. group:8. start_date:ddmmyy10. end_of_study:ddmmyy10. ;
datalines;
1 1 05-11-2002 01-01-2015
1 3 01-01-2014 01-01-2015
2 5 01-01-2000 31-12-2015
2 4 01-01-2002 31-12-2015
2 5 01-01-2014 31-12-2015
3 1 01-01-2000 27-07-2015
3 2 01-01-2008 27-07-2015
3 1 01-01-2010 27-07-2015
3 2 01-01-2011 27-07-2015
;
run;

proc sort data=have;
by id group start_date;
run;

data want(drop=lead_start lead_id);
format end_date ddmmyy10.;
merge have have(firstobs=2 rename=(start_date=lead_start id=lead_id) drop=end_of_study group );
if id=lead_id then end_date=lead_start-1;
else end_date=end_of_study;
run;