topic Removing observations based on other observations in SAS Programming

Removing observations based on other observations

DeepakSwain — Fri, 31 Mar 2017 19:23:41 GMT

Hi there,

For your kind information, I am analysing a dataset having patient's records related to his duration of stay in hospital. Some patient may have multiple records of admission during the same visit created by different departments of the same hospital. I am trying to remove all records which are reflecting part of the total duration of stay. For e.g id 101 having one record showing duration of stay from 20160110 to 20160120 whereas other records created by a department shows duration of stay from 20160115 20160118 which is actually part of his total stay. I want to delete the second record. Can some body help me to address this.

data have;
input id adm_date  dis_date  site ;
cards;
101 20160110 20160120 1234
101 20160115 20160118 1235
101 20160125 20160128 1235
102 20160110 20160118 1234
102 20160110 20160112 1235
;
run;


data want;
input id adm_date  dis_date  site ;
cards;
101 20160110 20160120 1234
101 20160125 20160128 1235
102 20160110 20160118 1234
;
run;

Thank you in advance for your kind reply.

Regards,

Re: Removing observations based on other observations

collinelliot — Fri, 31 Mar 2017 19:40:31 GMT

I'm not following your logic based on the "want" results.

You're keeping two records for 101, which suggests that the site is part of your grouping, but for 102 you only keep one record, even though they have different sites.

I think you're wanting to keep the records that have the largest stay, but I'm wondering if you can clarify.

Or just wait and see if others get it and give you what you want.

Re: Removing observations based on other observations

ballardw — Fri, 31 Mar 2017 19:47:19 GMT

What if you have data that looks like this:

101 20160110 20160120 1234
101 20160115 20160127 1235
101 20160125 20160128 1235

where you have a partially overlap and not completely within the duration of another visit? Would you still want the second record removed?

Re: Removing observations based on other observations

DeepakSwain — Fri, 31 Mar 2017 19:50:47 GMT

Hi collinelliot,
Here site is not a criteria, date range is the criteria. According to business rule, the largest admission period will be kept and sub-admission record will be deleted.
Regards,

Re: Removing observations based on other observations

DeepakSwain — Fri, 31 Mar 2017 19:57:43 GMT

Hi ballardw,

You read my mind. Actually I were waiting to raise this issue in my subsquent mail once I got partial solution to remove the observations having admission period within the maximum duration of admission.

In the second step, I will try to get the extended duration of stay after removing the overlapping period based on the scenario suggested by you, if possible.

In all cases, site number is having no importance.

Regards,

Re: Removing observations based on other observations

PGStats — Fri, 31 Mar 2017 19:58:02 GMT

Which site do you want to keep?

Re: Removing observations based on other observations

DeepakSwain — Fri, 31 Mar 2017 20:01:18 GMT

Hi PGStats,
Site having longer duration of stay will be my priority at first. Later if possible in case there is overlapping duration of stay. i will try to capture the longest duration of stay first, then after removing the overlap period , the extended duration of stay in other site.
Regards,

Re: Removing observations based on other observations

ballardw — Fri, 31 Mar 2017 20:59:12 GMT

For the base requirement I would start with this:

data have;
   informat id $5. adm_date  dis_date yymmdd8.  site $5.;
   format adm_date  dis_date yymmdd10.;
   input id adm_date  dis_date  site ;
cards;
101 20160110 20160120 1234
101 20160115 20160118 1235
101 20160125 20160128 1235
102 20160110 20160118 1234
102 20160110 20160112 1235
;
run;
proc sort data=have;
   by id adm_date dis_date;
run;

data reduced;
  set have;
  by id ;
  Ladm= lag(adm_date);
  Ldis= lag(dis_date);
  if not first.id and (ladm le adm_date le ldis) and (ladm le dis_date le ldis) then delete;
  drop ladm ldis;
run;

Note that I am using actual date values. I have a suspicion that somewhere down the line the inteval measures may be needed and determining the duration of the interval 20160228 to 20160305 isn't easy unless you have SAS date values.

Re: Removing observations based on other observations

PGStats — Sat, 01 Apr 2017 04:04:17 GMT

Here is something you could do. I added id=103, a more complex case involving a succession of stays.

data have;
   informat id $5. adm_date  dis_date yymmdd8.  site $5.;
   format adm_date dis_date yymmdd10.;
   input id adm_date dis_date site;
cards;
101 20160110 20160120 1234
101 20160115 20160118 1235
101 20160125 20160128 1235
102 20160110 20160118 1234
102 20160110 20160112 1235
103 20160110 20160112 1234
103 20160112 20160115 1235
103 20160115 20160120 1237
103 20160116 20160118 1236
;

data temp;
set have;
obs = _n_;
run;

proc sql;
create table overlap as
select a.obs as from, b.obs as to
from 
    temp as a inner join 
    temp as b on a.id=b.id and 
        b.adm_date between a.adm_date and a.dis_date; 
*select * from overlap;
quit;

%include "&sasforum\subgraphsmacro.sas" / source2;

%subgraphs(overlap);

proc sql;
create table stays as
select 
    a.clust as stayId,
    b.*
from clusters as a inner join 
    temp as b on a.node=b.obs
order by a.clust, b.adm_date, b.obs;
*select * from stays;
quit;

data want;
do until(last.stayId);
    set stays; by stayId;
    if first.stayId then do;
        adm_d = adm_date;
        adm_site = site;
        end;
    if dis_date >= dis_d then do;
        dis_d = dis_date;
        dis_site = site;
        end;
    end;
output;
format adm_d dis_d yymmdd10.;
keep id adm_d dis_d adm_site dis_site;
run;

proc print data=want noobs; run;

The subgraphs macro is attached.

Re: Removing observations based on other observations

Ksharp — Sat, 01 Apr 2017 14:06:21 GMT

Find them and Delete them.

data have;
input id adm_date  dis_date  site ;
cards;
101 20160110 20160120 1234
101 20160115 20160118 1235
101 20160125 20160128 1235
102 20160110 20160118 1234
102 20160110 20160112 1235
;
run;
data have;
 set have;
 by id;
 if first.id then n=0;
 n+1;
run;
proc sql;
create table key as
 select b.*
  from have as a,have as b 
   where a.id=b.id and a.adm_date le b.adm_date and
         a.dis_date gt b.dis_date and a.n ne b.n;
quit;
data want;
 if _n_=1 then do;
  if 0 then set key;
  declare hash h(dataset:'key');
  h.definekey(all:'y');
  h.definedone();
 end;
set have;
if h.check()=0 then delete;
run;