## Removing observations based on other observations

Solved
Frequent Contributor
Posts: 120

# 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;``````

Regards,

Swain

Accepted Solutions
Solution
‎04-01-2017 05:27 AM
Posts: 5,521

## 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.;
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
*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
*select * from stays;
quit;

data want;
do until(last.stayId);
set stays; by stayId;
if first.stayId then do;
end;
if dis_date >= dis_d then do;
dis_d = dis_date;
dis_site = site;
end;
end;
output;
run;

proc print data=want noobs; run;``````

The subgraphs macro is attached.

PG

All Replies
PROC Star
Posts: 311

## 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: 120

## 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: 13,498

## 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: 120

Hi  ballardw,

Swain
Super User
Posts: 13,498

## Re: Removing observations based on other observations

```data have;
informat id \$5. adm_date  dis_date yymmdd8.  site \$5.;
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;
run;

data reduced;
set have;
by id ;
Ldis= lag(dis_date);
if not first.id and (ladm le adm_date le ldis) and (ladm le dis_date le ldis) then delete;
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.

Posts: 5,521

## Re: Removing observations based on other observations

Which site do you want to keep?

PG
Frequent Contributor
Posts: 120

## 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
Posts: 5,521

## 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.;
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
*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
*select * from stays;
quit;

data want;
do until(last.stayId);
set stays; by stayId;
if first.stayId then do;
end;
if dis_date >= dis_d then do;
dis_d = dis_date;
dis_site = site;
end;
end;
output;
run;

proc print data=want noobs; run;``````

The subgraphs macro is attached.

PG
Super User
Posts: 10,761

## 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
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.