Hi guys,
suppose to have the following two datasets:
data DB;
input ID :$20. Admission :date09. Discharge :date09.;
format Admission date9. Discharge date9.;
cards;
0001 04FEB2017 22FEB2017
0002 10JUN2017 16JUN2017
0004 29NOV2017 01DEC2017
0005 28DEC2017 05JAN2018
;
and
data DB1;
input Start :date09. End :date09. Index;
format Start :date9. End :date9.;
cards;
29JAN2017 04FEB2017 1
04JUN2017 10JUN2017 1
26NOV2017 02DEC2017 1
24DEC2017 30DEC2017 1
31DEC2017 06JAN2018 1
;
Desired output:
data DBx;
input Start :date09. End :date09. Index;
format Start :date9. End :date9.;
cards;
29JAN2017 04FEB2017 1
04JUN2017 10JUN2017 1
26NOV2017 02DEC2017 1
24DEC2017 30DEC2017 1
;
The DB dataset contains a set of admissions with relative date of admission and date of discharge. The duration of the admission is not strictly one week but the time the patient needs.
The DB1 dataset (a small par is reported for simplicity) contains the week (Start-End) of the year where the admission occurred (referring to "Admission" variable of DB). For example, for ID 0001 the admission occurred at 04FEB2017 that is in the week 29JAN2017-04FEB2017 of year 2017.
The problem is with ID = 0005 because two weeks are counted in DB1 but no new admission occurred for ID 0005. The patient only stayed hospitalized. The count should be 0.
Here the code used to generate DB1:
%macro Counts(dataset);
data &dataset;
set &dataset;
week_begin = intnx('week', Admission, 0);
format week_begin date9.;
run;
proc freq data=&dataset;
tables week_begin / out=admissions&dataset (keep=week_begin count rename=(count=n_&dataset)) noprint;
run;
proc sql;
create table Counts_&dataset as
select d1.*, d2.n_&dataset
from all_weeks d1 left join admissions&dataset d2
on d2.week_begin >= d1.admission and d2.week_begin <= d1.discharge
;
quit;
proc sort data = Counts_&dataset; by admission; run;
data TSM_&dataset;
set Counts_&dataset;
if missing (n_&dataset) then n_&dataset = 0;
run;
%mend;
the dataset all_weeks looks like this:
data All_weeks;
input Admission :date09. Discharge :date09.;
format Admission date9. Discharge date9.;
cards;
29DEC2013 04JAN2014
05JAN2014 11JAN2014
12JAN2014 18JAN2014
19JAN2014 25JAN2014
26JAN2014 01FEB2014
.....
;
Can anyone help me please?
Thank you in advance
... View more
