Adding an observation depending on other observations

[RTN]

Hi

I am new to SAS 9.4 and I need help in adding a new observation depending on other observations.

I have a data set with people with 1 or more observations. I have an ID, group, study_start, study_end, group_date. I have more variables per observation that are irrelevant here. The other variables are the same for every ID, they don't change even if it is a new observation.

What I want is to add an observation IF the group_date is after study_start. Then I want an observation where group is 1 and study_start is the same as the previous one and study_end is the same as the previous group-date -1. I have organized it in descending year to make it easier to add an observation. It is not so important to get the study_end; I can manage that once I get the new observation. It is however, important that I get all my variables copied to the new observation and that the group =1.

Hope somebody can help me.

data have;
input ID group study_start study_end group_date;

1 1 2013 2015 2013
1 2 2000 2012 2011

2 3 2000 2015 2004

3 2 2004 2009 2006

4 3 2009 2015 2009 
4 2 2006 2008 2006

run;

data want;
input ID group study_start study_end group_date;

1 1 2013 2015 2013
1 2 2000 2012 2011
1 1 2000 2010 

2 3 2000 2015 2004
2 1 2000 2003

3 2 2004 2009 2006
3 1 2004 2005

4 3 2009 2015 2009 
4 2 2000 2008 2006
4 1 2000 2005
; run;

Thank you in advance,

Regards

Rikke

[r_behata · Accepted solution]

data have;
input ID group study_start study_end group_date;
cards;
1 1 2013 2015 2013
1 2 2000 2012 2011
2 3 2000 2015 2004
3 2 2004 2009 2006
4 3 2009 2015 2009 
4 2 2000 2008 2006
;
run;

data want;
	set have;
	by id;

	output;

	if last.id then do;
		if  study_start < group_date then do;
			study_end=group_date-1;
			group_date=.;output;
		end;
	end;
run;

[franriv]

data have;
	input ID group study_start study_end group_date;
	datalines;
1 1 2013 2015 2013
1 2 2000 2012 2011
2 3 2000 2015 2004
3 2 2004 2009 2006
4 3 2009 2015 2009 
4 2 2006 2008 2006
;
run;

data want;
	set have;
	by id;
	output;
	if last.id then do;
		group=1;
		study_end=group_date-1;
		group_date=.;
		output;
	end;
run;