Transform a dataset of date consumption of two medications into weeks based on the period of drug A

[tSAS1]

I have a dataset like this: 

Patient ID	Drug	date	days of consumption	dose
1	A	01/01/2018	28	1056
1	A	29/01/2018	21	800
1	A	19/02/2018	28	2000
1	B	05/03/2018	28	12000
1	A	02/04/2018	21	900
2	A	03/03/2019	28	1300

I want to transform the data into weeks of consumption, based on the period of drugA and the dataset that i want is like shown below:

Patient ID	Drug	Week	weekly_dose
1	A	1	264
1	A	2	264
1	A	3	264
1	A	4	264
1	A	5	266.6666667
1	A	6	266.6666667
1	A	7	266.6666667
1	A	8	500
1	A	9	500
1	A	10	500
1	A	11	500
1	B	10	3000
1	B	11	3000
1	A	12	0
1	A	13	0
1	B	12	3000
1	B	13	3000
1	A	14	300
1	A	15	300
1	A	16	300
2	A	1	325
2	A	2	325
2	A	3	325
2	A	4	325

My code is not really helpful because I created the data per weeks, and not based on the weeks of drug A

data DrugA;
set data(where=(drug="A"));
do i=1 to (days_cosumption / 7);
		weeks=cats("Week",i);
		weekly_dose=dose/(days_cosumption / 7);
		output;
	end;
run;
data DrugB;
set data(where=(drug="B"));
do i=1 to (days_cosumption / 7);
		weeks=cats("Week",i);
		weekly_dose=dose/(days_cosumption / 7);
		output;
	end;
run;
data Drug_A_B;
set DrugA DrugB;
run;

and this is to plot the consumption of the two medications for each ID over drugA weeks as this example of plot

[andreas_lds]

You may want to explain, why week starts with 10 (10 seems to be the number of the week) for

1

B

05/03/2018

28

12000

but with 1 for

2

A

03/03/2019

28

1300

[tSAS1]

@andreas_lds  for the first ID this line
1 A 19/02/2018 28 2000
means that starting from week8 the patient1 started consuming DrugA for 4weeks (from week8 to week11) until 19/03/2018 and he started consuming DrugB starting from 05/03/2018 for 4weeks.
it means he consumed both drugs in same two weeks (week10 and 11),

In other words he started taking drugB from the 10th week of drugA.

And patient2 took only drugA for 4weeks, that's why his weeks started from 1 until 4.

[yabwon · Accepted solution]

With data you provided, this seems to be doing the job:

data have;
input patient_ID	Drug $	date ddmmyy10.	days_of_consumption	dose;
format date ddmmyy10.;
cards;
1	A	01/01/2018	28	1056
1	A	29/01/2018	21	800
1	A	19/02/2018	28	2000
1	B	05/03/2018	28	12000
1	A	02/04/2018	21	900
2	A	03/03/2019	28	1300
;
run;
proc print;
run;

data want1;
  set have;

  dt = date;
  d = drug;
  do _N_ = 0 to days_of_consumption-1;
    daily_dose = dose/days_of_consumption;
    date = dt + _N_; 
    drug = d;
    output;
    if d ^= "A" then     /* hardcoding */
      do;
        drug = "A";      /* hardcoding */
        daily_dose = 0;  /* hardcoding */
        output;
      end;
  end;
  drop weeks days_of_consumption dt dose d;
run;

proc sort data = want1;
  by patient_ID date drug;
run;

data want2;
  set want1;
  by patient_ID date;
  if first.patient_ID then week = 0;
  days + first.date;
  week + (mod(days,7)=1)*first.date;
run;

proc sql;
create table want3 as
  select patient_ID, drug, week, sum(daily_dose) as weekly_dose
  from want2
  group by 1,2,3
  order by 1,3,2
; 
quit;

proc print;
run;

Bart

[tSAS1]

Many thanks 🙂