topic Re: How to retain/remove dates by unique member? in SAS Programming

How to retain/remove dates by unique member?

mrafael03 — Thu, 30 Apr 2020 15:13:08 GMT

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;

Re: How to retain/remove dates by unique member?

Kurt_Bremser — Thu, 30 Apr 2020 16:04:37 GMT

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

Re: How to retain/remove dates by unique member?

mrafael03 — Fri, 01 May 2020 18:06:48 GMT

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?

Re: How to retain/remove dates by unique member?

mrafael03 — Tue, 01 Sep 2020 14:58:34 GMT

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~~

Re: How to retain/remove dates by unique member?

Kurt_Bremser — Tue, 01 Sep 2020 15:13:50 GMT

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?

Re: How to retain/remove dates by unique member?

mrafael03 — Tue, 01 Sep 2020 15:29:55 GMT

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.

Re: How to retain/remove dates by unique member?

Kurt_Bremser — Tue, 01 Sep 2020 16:01:28 GMT

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.

Re: How to retain/remove dates by unique member?

mrafael03 — Tue, 01 Sep 2020 16:27:15 GMT

Thank you!