BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
silversta
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

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:

 

 

dcheck.gif

 

 

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20
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
Super User

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
Jade | Level 19
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
Rhodochrosite | Level 12

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:

 

 

dcheck.gif

 

 

silversta
Calcite | Level 5

Thank you all for your help! This worked marvelously!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1634 views
  • 1 like
  • 5 in conversation