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,
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.
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.
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?
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,
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.
Which site do you want to keep?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.