BookmarkSubscribeRSS Feed
Bumble_15
Fluorite | Level 6

Hello,

I am relatively new to SAS and I am having trouble with a step.  I have a dataset that contains a patient identifier, an admission identifier (e.g. 1st, 2nd, 3rd), the admission date and and indicator for whether this was an "index" admission. 

data have;                    
	format adm_date e8601da.;
	input patient admission adm_date mmddyy10. index;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 1
1 3 08/12/2013 0
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 1
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;


Some patients have two index admissions (see patient 1 and 2 ) but I want each patient to have only one index admission, which would be the 1st of the two admissions (based on date). 

 

Thank you!

9 REPLIES 9
Mazi
Pyrite | Level 9
data have;                    
	format adm_date e8601da.;
	input patient admission adm_date mmddyy10. index;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 1
1 3 08/12/2013 0
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 1
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;

data want;
	set have;
	by patient index notsorted;
	if ~first.index and index=1 then index=0;
run;

proc compare b=want c=have listall;
run;

Can you try this?

Bumble_15
Fluorite | Level 6

Thank you for getting back to me! 

That code mostly worked but I actually have two patients where the data looks like patient 1 below, where the second index admission does not correspond to the 2nd admission. Sorry I didn't add that in before.

 

ata have;                    
	format adm_date e8601da.;
	input patient admission adm_date mmddyy10. index;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 0
1 3 08/12/2013 1
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 1
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;

 

 

Mazi
Pyrite | Level 9
In this case, what should happen?
Bumble_15
Fluorite | Level 6

I am still hoping to retain the first visit marked index as the only index value.

So what I have is:

 

data have;                    
	format adm_date e8601da.;
	input patient admission adm_date mmddyy10. index;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 0
1 3 08/12/2013 1
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 1
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;

and what I want is:

data have;                    
	format adm_date e8601da.;
	input patient admission adm_date mmddyy10. index;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 0
1 3 08/12/2013 0
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 0
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;

Thanks!

 

Kurt_Bremser
Super User

So you do not want to remove the additional index 9bservation, you want to make it "non-index".

data want;
set have;
by patient adm_date;
retain found;
if first.patient then found = 0;
if found then index = 0;
if index then found = 1;
drop found;
run;
Mazi
Pyrite | Level 9
data have;                    
	format adm_date e8601da.;
	input patient admission adm_date mmddyy10. index;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 0
1 3 08/12/2013 1
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 1
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;

data want;
	set have;
	by patient;
	if first.patient then x=0;
	if index then x+1;
	if x>1 then index=0;
	drop x;
run;

data want2;                    
	format adm_date e8601da.;
	input patient admission adm_date mmddyy10. index;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 0
1 3 08/12/2013 0
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 0
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;


proc compare b=want c=want2 listall;
run;

This should do it

Ksharp
Super User
data have;                    
	input patient admission adm_date mmddyy10. index;
    format adm_date e8601da.;
	datalines;
1 1 01/12/2013 1
1 2 06/24/2013 0
1 3 08/12/2013 1
2 1 02/10/2013 0
2 2 01/05/2014 1
2 3 03/06/2014 1
3 1 02/11/2011 1
3 2 01/12/2012 0
4 1 03/21/2010 0
4 2 04/06/2010 0
4 3 09/05/2015 1
4 4 09/11/2016 0
;
run;

data want;
   set have;
   by patient;
   retain found 0;
   if first.patient then found=0;
   if index=1 and found then index=0;
   if index=1 and not found then found=1;
drop found;
 run;
Kurt_Bremser
Super User
data want;
set have;
by patient adm_date;
retain found;
if first.patient then found = 0;
if found and index then delete;
if index then found = 1;
drop found;
run;
Tom
Super User Tom
Super User

Make a NEW dataset with a NEW variable that has ONE observation per patient.

data index_date;
  set have;
  where index=1;
  by patient;
  if first.patient;
  keep patient adm_date;
  rename adm_date=index_date;
run;

That way if you need to combine with the original dataset you will not have any name conflicts.

data want;
  merge index_date have;
  by patient;
run;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1146 views
  • 2 likes
  • 5 in conversation