How to select value by date?

[Bumble_15]

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!

[Mazi]

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]

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]

In this case, what should happen?

[Bumble_15]

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]

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]

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]

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]

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]

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;