Transform a datasets based on dates information to dataset based on weeks

[SAS03]

Hello everyone,

I have a data set which contains the clients ID, the days of the product consumption and the quantity consumed like this:

Client_ID	date	consumption_days	quantity
Client_1	01/12/2015	28	1200
Client_1	29/12/2015	7	320
Client_1	05/01/2016	28	700
Client_2	01/02/2017	7	170
Client_2	08/02/2017	35	880

Want I want is to transform the data into weeks instead of dates, and so the quantities also would be per week as shown below:

Client_ID	Weeks	quantity
Client_1	week1	300
Client_1	week2	300
Client_1	week3	300
Client_1	week4	300
Client_1	week5	320
Client_1	week6	175
Client_1	week7	175
Client_1	week8	175
Client_1	week9	175
Client_2	week1	170
Client_2	week2	176
Client_2	week3	176
Client_2	week4	176
Client_2	week5	176
Client_2	week6	176

I tried to calculate the number of weeks of each client and the quantity consumed per week with this code:

data want;
set have;
nb_weeks=consumption_days/7;
quantity_per_week=quantity/nb_weeks;
run;

and then I tried many ways to transform it into weeks but it doesn't work.

Thank you in advance!

[PeterClemmensen · Accepted solution]

Try this

data have;
input Client_ID $ date : ddmmyy10. consumption_days quantity;
format date ddmmyy10.;
datalines;
Client_1 01/12/2015 28 1200
Client_1 29/12/2015 7 320
Client_1 05/01/2016 28 700
Client_2 01/02/2017 7 170
Client_2 08/02/2017 35 880
;

data want(keep = Client_ID weeks q);
   set have;
   do i = 1 to (consumption_days / 7);
      weeks = cats('Week', i);
      q = quantity / (consumption_days / 7);
      output;
   end;
run;

Result:

Client_ID  weeks  q 
Client_1   Week1  300 
Client_1   Week2  300 
Client_1   Week3  300 
Client_1   Week4  300 
Client_1   Week1  320 
Client_1   Week1  175 
Client_1   Week2  175 
Client_1   Week3  175 
Client_1   Week4  175 
Client_2   Week1  170 
Client_2   Week1  176 
Client_2   Week2  176 
Client_2   Week3  176 
Client_2   Week4  176 
Client_2   Week5  176 

[SAS03]

Thank you

[PeterClemmensen]

Anytime