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!
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
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
Anytime
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.