DATA Step, Macro, Functions and more

Removing observations based on other observations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

Removing observations based on other observations

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,

 

Swain

Accepted Solutions
Solution
‎04-01-2017 05:27 AM
Respected Advisor
Posts: 4,651

Re: Removing observations based on other observations

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.

PG

View solution in original post

Attachment

All Replies
PROC Star
Posts: 288

Re: Removing observations based on other observations

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.

Frequent Contributor
Posts: 96

Re: Removing observations based on other observations

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,
Swain
Super User
Posts: 10,511

Re: Removing observations based on other observations

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?

 

 

Frequent Contributor
Posts: 96

Re: Removing observations based on other observations

Hi  ballardw,

 

 

 

 

 

Swain
Super User
Posts: 10,511

Re: Removing observations based on other observations

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.

 

Respected Advisor
Posts: 4,651

Re: Removing observations based on other observations

Which site do you want to keep?

PG
Frequent Contributor
Posts: 96

Re: Removing observations based on other observations

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,
Swain
Solution
‎04-01-2017 05:27 AM
Respected Advisor
Posts: 4,651

Re: Removing observations based on other observations

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.

PG
Attachment
Super User
Posts: 9,682

Re: Removing observations based on other observations

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 231 views
  • 6 likes
  • 5 in conversation