How to retain/remove dates by unique member?

[mrafael03]

Hello,

I'll start by providing the requirement

• Blue highlighted dates are the expected results
• DAYS_DIFF=1st DISCH_DT-NEXT ADMIT_DT

1. If the discharge date is followed by readmission or direct transfer to an inpatient care setting within 30-day follow-up period, count only the last discharge date.

e.g.

ID     ADMIT_DT   DISCH_DT   DAYS_DIFF

011    1/10/2018       1/13/2018         16      <<< "readmission within 30 days - only include last discharge"

011    1/29/2018       2/28/2018

022    2/13/2018        2/17/2018        55      <<< "readmission >30 days"
022    4/13/2018        4/25/2018

2. For member with same admission date, but different discharge dates; use the last discharge date.

e.g.

ID    ADMIT_DT   DISCH_DT 

033   12/11/2018     12/12/2018
033   12/11/2018     12/17/2018   

3. For member with same discharge date, but different admit date; use the first admit date.

e.g.

ID    ADMIT_DT   DISCH_DT 

033  6/11/2018     6/17/2018
033  6/13/2018     6/17/2018

4. For member with the same 1st discharge date and next admit date, count the 1st admit and next discharge date.

e.g.

ID     ADMIT_DT   DISCH_DT  DAYS_DIFF

033  08/11/2018    08/17/2018        0
033  08/17/2018     08/23/2018

I would like to loop through the dataset. See my codes below. I'm still a newbie in SAS and trying to figure out how to retain the dates based on the criteria.  Any help would be greatly appreciated. Thank you!

data dataset1;
input id $1-3 admit_dt 4-12 disch_dt 13-21;
datalines;
011 20180110 20180113
011 20180129 20180228
022 20180213 20180217
022 20180413 20180425
033 20181211 20181212
033 20181211 20181217
033 20180611 20180617
033 20180613 20180617
033 20180811 20180817
033 20180817 20180823
;
run;

data dataset2;
set dataset1;
admit_dt=input(put(admit_dt,8.),yymmdd8.);
disch_dt=input(put(disch_dt,8.),yymmdd8.);
format admit_dt disch_dt mmddyy10.;
run;

proc sort data=dataset2;
by id admit_dt disch_dt;
run;


data result;
set dataset2;
daygap=0;
retain strt_dt end_dt;
format strt_dt end_dt mmddyy10.; 
by ;
if first.id then 
     do;
     strt_dt=admit_dt;
     end_dt=disch_dt;
end;
     else if intck('day',end_dt,admit_dt) <=30 and strt_dt>admit_dt
     then do;
     daygap=intck('day',end_dt,admit_dt);    
     end_dt=disch_dt;
	 end;

	 else if intck('day',end_dt,admit_dt) >30
     then do;
     daygap=intck('day',end_dt,admit_dt);
     strt_dt=admit_dt; 
     end_dt=disch_dt;
     end;

     else do;
     daygap=intck('day',end_dt,admit_dt);
     strt_dt=admit_dt;
     end_dt=disch_dt;
end;
/*drop adms_dt disch_dt daygap;*/
/*if last.sbscr_id;*/
run;

[Kurt_Bremser · Accepted solution]

Make dates SAS dates when reading them in the first place.

See this:

data have;
input id $1-3 admit_dt :yymmdd8. disch_dt :yymmdd8.;
format admit_dt disch_dt yymmddd10.;
datalines;
011 20180110 20180113
011 20180129 20180228
022 20180213 20180217
022 20180413 20180425
033 20181211 20181212
033 20181211 20181217
033 20180611 20180617
033 20180613 20180617
033 20180811 20180817
033 20180817 20180823
;

proc sort data=have;
by id admit_dt disch_dt;
run;

data want;
merge
  have
  have (firstobs=2 rename=(id=_id admit_dt=_admit_dt disch_dt=_disch_dt))
;
retain _del_flag;
if _del_flag
then do;
  _del_flag = 0;
  delete;
end;
if id = _id
then do;
  if disch_dt = _admit_dt
  then do;
    disch_dt = _disch_dt;
    _del_flag = 1;
  end;
  else if disch_dt = _disch_dt then _del_flag = 1;
  else if _admit_dt - disch_dt < 30 then delete;
end;
drop _:;
run;

proc print data=want noobs;
run;

Result:

id	admit_dt	disch_dt
011	2018-01-29	2018-02-28
022	2018-02-13	2018-02-17
022	2018-04-13	2018-04-25
033	2018-06-11	2018-06-17
033	2018-08-11	2018-08-23
033	2018-12-11	2018-12-17

[mrafael03]

Hello @Kurt_Bremser ,

I'm trying the code that you've provided regarding retain function. So date of service must be after 31 days prior to the date of visit and if it's within 31 days of visits it should be removed.

e.g.

If the id has a dates of May 1 and had the next dates of May 3, May 6 and June 30. I am only including the dates of May 1 and June 30 because the difference was more than 31 days.

data dataset1;
input id $1-3 dos 4-12;
datalines;
033 20200725 
033 20200804 
033 20201011 
033 20201013 
033 20201213 
044 20200713 
044 20200720 
044 20200804 
011	20200502
011	20200505
011	20200701
;
run;


data dataset2;
set dataset1;
dos=input(put(dos,8.),yymmdd8.);
format dos mmddyy10.;
run;


proc sql;
create table dataset3 as select distinct
id
,dos                 
from dataset2
order by id, dos
;
quit;

proc sort data=dataset3;
by id dos;
run;

data want;
merge
  dataset3
  dataset3 (firstobs=2 rename=(id=_id dos=_dos))
;
retain _del_flag;
if _del_flag
then do;
  _del_flag = 0;
  delete;
end;
if id = _id
then do;
  if dos = _dos
  then do;
    dos = _dos;
    _del_flag = 1;
  end;
  else if dos = _dos then _del_flag = 1;
  else if _dos - dos <=31 then _del_flag = 1;
end;
drop _:;
run;

Expected Result:

011 05/02/2020
011 07/01/2020
033 07/25/2020
033 10/11/2020
033 12/13/2020
044 07/13/2020
044 08/04/2020

[Kurt_Bremser]

Let me rephrase it:

• the first date of a group is a starting date, and is kept
• everything within 31 days of the starting date is deleted
• a date after those 31 days is kept, and becomes the new starting date

Is that correct?

[mrafael03]

Yes that's correct.

If the id has more than one service in a 31-day period, include only the first date.
e.g, if there's a first date on May 1, include the May 1 date and do not include the dates that occur between May 2 and May 31; then, if applicable, include the next date that occurs on or after June 1. Identify dates, including only one per 31-day period.

[Kurt_Bremser]

data want;
set have;
by id;
retain start_date;
if first.id then start_date = dos;
else do;
  if dos - start_date le 31 then delete;
  else start_date = dos;
end;
drop start_date;
run;

Untested, posted from my tablet.

[mrafael03]

Thank you!

[mrafael03]

Awesome!!! Thanks for your help

Also, regarding this criteria

1. If the discharge date is followed by readmission or direct transfer to an inpatient care setting within 30-day follow-up period, count only the last discharge date.

 else if _admit_dt - disch_dt < 30 then delete;

data have;
input id $1-3 admit_dt :yymmdd8. disch_dt :yymmdd8.;
format admit_dt disch_dt yymmddd10.;
datalines;
044 20180122 20180123
044 20180214 20180215
044 20180215 20180222
044 20180305 20180306
;

Code Result:

id admit_dt disch_dt

044	2018-02-14	2018-02-22
044	2018-03-05	2018-03-06

Is the expected result should only be

044

2018-03-05

2018-03-06

as the difference between discharge date and admit date was within 12 days?