topic Re: Transform a dataset of date consumption of two medications into weeks based on the period of dru in SAS Programming

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

tSAS1 — Thu, 04 Mar 2021 10:32:55 GMT

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

Re: Transform a dataset of date consumption of two medications into weeks based on the period of dru

andreas_lds — Thu, 04 Mar 2021 10:28:00 GMT

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

05/03/2018

12000

but with 1 for

03/03/2019

1300

Re: Transform a dataset of date consumption of two medications into weeks based on the period of dru

tSAS1 — Thu, 04 Mar 2021 10:55:41 GMT

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

Re: Transform a dataset of date consumption of two medications into weeks based on the period of dru

yabwon — Thu, 04 Mar 2021 11:17:37 GMT

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

Re: Transform a dataset of date consumption of two medications into weeks based on the period of dru

tSAS1 — Thu, 04 Mar 2021 17:03:52 GMT

Many thanks 🙂