QUANTITY AND CALCULATE BALANCE AND CASE PACK

[niloya]

	QTY	CASE PACK
Day 1	14	6
Day 2	12	6
DAY 3	3	6
DAY 4	1	6
DAY 5	3	6

OUTPUT
Remaining qty	CASE PACK QTY
4	18
4	12
1	0
0	0
3	6

The quantities(QTY) are  sent to customer regularly. But I would like to send these quantities in case pack (6 PCS) based on the quantity column.            

For example, First day customer needs 14 pcs but we should send 18 pcs based on case pack qty (6*3=18) which is greater than customer request .              

So, the customer will use 14 out of 18 then the remaining qty in customer's hand is 4 pcs.Customer has 4 pcs end of the day 1.

In day 2 customer needs 12 pcs so we need to send as much case pack of 6 units        So, in day 2 the minimum we should send   12 pcs but customer already has 4 pcs . After 12 pcs is sent to the customer, so the customer will have 16 on day 2 but the customer spends 12 out of 16 then the remaining quantity is 4 pcs.

Day 3, The customer has 4 pcs and he needs only 3 pcs so he used 3 out of 4 and then he has 1 pcs at the end of day 3.

Day 4, he has 1 pc and he needs 1 pc so we don’t have to send any case pack. Day 5, he needs 3 pc but he does not have any quantity so we need to send  6 pcs (1 case pack).  He will use 3 pcs out of 6 then the remaining qty will be 3.     

Would you please help me to resolve this problem? How to create sas code for the output? 

[novinosrin · Accepted solution]

Hi @niloya 

data have;
input day $	QTY	CASEPACK ;
cards;
Day1	14	6
Day2	12	6
DAY3	3	6
DAY4	1	6
DAY5	3	6
;

data want ;
set have; 
k=ceil((qty-remaining)/casepack);
CASEPACKQTY=k*casepack;
remaining + CASEPACKQTY-qty;
drop k;
run;

[niloya]

@novinosrin

Thank you for your reply. How did you find remaining qty? I need to find remaining qty as well.

[niloya]

Hi @novinosrin

Thanks again for your reply .  tried the code you sent but it does not work. I need the number of number of case pack and remaining qty together.  do you think is there any other method to find them? 

[novinosrin]

Please read, understand, execute, review the code, output thoroughly and let us know your questions.

[niloya]

Hi
k=ceil((qty-remaining)/casepack);
CASEPACKQTY=k*casepack;
remaining + CASEPACKQTY-qty;
drop k;

I applied that code. Since i dont have remaining input in your code steps, the code did not work. When apply the code steps you recommend , it returns missing data. I think all your calculation depends on remaining input but this also is required to get sas steps to find
My inputs are
Input output
QTY CASE PACK
Day 1 14 6
Day 2 12 6
DAY 3 3 6
DAY 4 1 6
DAY 5 3 6

[niloya]

@novinosrin
You are the best ever 🙂 I am really sorry that i was skipping the parenthesis right next to ceil formulation. That is why i have not found the result. You did a perfect job Thank you so much

[niloya]

Hi @novinosrin

Thank you for your replies. I have a quick question. I really appreciate your help.  I need your help again. 

I have to run this code for too many different items separately. If i add the items as a big list like that below, how sas codes changes? 

Thank you so much again. 

data have ;
input day $ item $ qty casepack;
cards;
day1  a 1 6
day2  a 1 6
day3  a 1 6
day4  a 1 6
day5  a 1 6
day1  b 1 5
day2  b 2 5
day3  b 4 5
day1  c 3 4
day2  c 4 4
day3  c 2 4

;

data want;
set have;
by item;
k=ceil((qty-remaining)/casepack);
casepackqty=k*casepack;
remaining+casepackqty-qty;
drop k;
run;

[novinosrin]

by item;
if first.item then remaining=0;

[niloya]

Thank you so much . You are the life saver. Million times thank you. It is working:)

[Reeza]

Your stated expected output:

---

@niloya wrote:

OUTPUT
Remaining qty	CASE PACK QTY
4	18
4	12
1	0
0	0
3	6

---

The variables Remaining and CasePackQTY are matching to what you expected. The input is exactly as you've specified, no additional data is used, I ran the program from @novinosrin  as well.

I'm assuming you changed the code to refer to your data set? If it doesn't work, please explicitly explain how it does not work and what your expected output is for the new data. 

Most likely you forgot to include a use case or didn't change the code correctly to account for your data set or variable names. 

Please include the code used if you're still having issues. 

[niloya]

@Reeza

Thank you so much helping me to figure out my mistake:)