topic Finding the first date variable 90 days out from another date variable in SAS Programming

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

silversta — Mon, 10 Feb 2020 19:31:14 GMT

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!

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

novinosrin — Mon, 10 Feb 2020 19:40:51 GMT

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;

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

Kurt_Bremser — Tue, 11 Feb 2020 07:28:49 GMT

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;

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

andreas_lds — Tue, 11 Feb 2020 16:09:02 GMT

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

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

ErikLund_Jensen — Tue, 11 Feb 2020 16:27:37 GMT

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:

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

silversta — Thu, 09 Apr 2020 16:14:15 GMT

Thank you all for your help! This worked marvelously!