Finding the first date variable 90 days out from another date variable

[silversta]

Hello all,

I have identified claims for a specific injury type and the injury can reoccur on different dates. My data was originally set up this way with multiple rows per person (each row is a claim for each injury). 

ID      admission_date

1       03Feb2009

1       05Feb2009

1       14Jun2009

2       25Oct2011

3       19Sept2008

3       04Jan2010

I subsequently transposed the data using the code below since I need the data in wide format for subsequent analyses.

*transpose so that multiple injury admission dates are separate variables;
proc sort data=inj_all; by id; run;
proc transpose data=in_all out=inj_wide_dt (drop=_NAME_ _LABEL_)
prefix=injury_date;
var admission_date;
by id;
run;

The data now looks like this:

ID   injury_date1  injury_date2  injury_date3

1    03Feb2009   05Feb2009   14Jun2009

2    25Oct2011

3    19Sep2008  04Jan2010

For those who have more than one injury date, I need to take the first date that is more than 90 days out from the first injury date...so in the case of ID 1, that would be 14Jun2009 since that is more than 90 days past 03Feb2009 and for ID 3, that would be 04Jan2010. Is there a way to do this in SAS? Ultimately, I just need to create an indicator, 1 if there is a date >90 days from injury_date1, and 0 otherwise. Any help provided would be greatly appreciated!

Best!

[novinosrin]

data have;
input ID      admission_date :date9.;
format admission_date date9.;
cards;
1       03Feb2009
1       05Feb2009
1       14Jun2009
2       25Oct2011
3       19Sep2008
3       04Jan2010
;

proc sql;
create table want as
select a.*,intck('days',m,admission_date)>90 as indicator
from have a left join (select id,min(admission_date) as m  from have group by id)b
on a.id=b.id
order by id,admission_date;
quit;

 More concised form of the above would be remerge automatically like below

proc sql;
create table want as
select *,intck('days',min(admission_date),admission_date)>90 as indicator
from have
group by id
order by  id,admission_date;
quit;

[Kurt_Bremser]

Creating the indicator in a data step:

data have;
input ID      admission_date :date9.;
format admission_date date9.;
cards;
1       03Feb2009
1       05Feb2009
1       14Jun2009
2       25Oct2011
3       19Sep2008
3       04Jan2010
;

proc transpose data=have out=wide (drop=_NAME_)
prefix=injury_date;
var admission_date;
by id;
run;

data ind (keep=id indicator);
set have;
by id;
retain start;
if first.id then start = admission_date;
if last.id;
indicator = admission_date ge start + 90;
run;

data want;
merge
  wide
  ind
;
by id;
run;

[andreas_lds]

Maybe @silvestra, you should add another example to the data you have posted, so that it is not always the last date to select.

[ErikLund_Jensen · Accepted solution]

Hi @silversta 

In this case, a simple solution would be a data step instead of transpose and/or proc sql; Retain first date for each ID and check following records against that:

data have;
	input ID admission_date :date9.;
	format admission_date date9.;
	cards;
1       03Feb2009
1       05Feb2009
1       14Jun2009
2       25Oct2011
3       19Sep2008
3       04Jan2010
;
run;

data want (drop=firstdate); 
	set have;
	by ID;
	retain firstdate;
	if first.ID then firstdate = admission_date;
	if admission_date > firstdate + 90 then flag = 1;
	else flag = 0;
run;

Result:

[silversta]

Thank you all for your help! This worked marvelously!