topic Re: Assign events to weeks of year in New SAS User

Assign events to weeks of year

NewUsrStat — Fri, 04 Apr 2025 12:01:18 GMT

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

Re: Assign events to weeks of year

quickbluefish — Fri, 04 Apr 2025 10:28:59 GMT

It's hard to tell what your desired output is. You're just wanting to have a count of admissions by calendar week?

Re: Assign events to weeks of year

Kurt_Bremser — Fri, 04 Apr 2025 10:36:53 GMT

Use the WEEK function on the admission date, and then PROC FREQ to count the number of admissions per week.

Re: Assign events to weeks of year

NewUsrStat — Fri, 04 Apr 2025 10:40:19 GMT

Exactly. I just need to put the admission in the calendar week where it occurred. The problem here is that although the patient 0005 has not a new admission but he/she simply stay in hospital it is counted as a new admission.

Re: Assign events to weeks of year

quickbluefish — Fri, 04 Apr 2025 11:53:47 GMT

Do you mean 0005 is counted twice (incorrectly) because this person's hospital stay spans more than one week? I think you just need to ignore the discharge date (as @Kurt_Bremmer says below). From what I can tell based on your request, the discharge date is irrelevant. You just need to know how many admission dates fall within a given calendar week. That being said, I don't understand what 'index' means in DB1 (sorry, did not read all your code) and I don't understand why DB1 only contains some calendar weeks but not all. It would be helpful to know what your desired output file would look like.

Re: Assign events to weeks of year

NewUsrStat — Fri, 04 Apr 2025 11:59:13 GMT

Yes, what I need is to "annotate" the week of the admission and not the stay. Index means that there was an admission in that week. DB1 contains only the calendar weeks where there was at least one admission based on DB. i will add the desired output.

Re: Assign events to weeks of year

Kurt_Bremser — Fri, 04 Apr 2025 13:39:18 GMT

Do you want a count of admissions in a given week, or just a flag that there was at least one admission?