topic QUANTITY AND CALCULATE BALANCE AND CASE PACK in SAS Programming

QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Tue, 11 Jun 2019 23:55:11 GMT

	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?

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

novinosrin — Wed, 12 Jun 2019 00:46:45 GMT

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;

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Wed, 12 Jun 2019 02:09:45 GMT

@novinosrin

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

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Wed, 12 Jun 2019 02:26:49 GMT

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?

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

novinosrin — Wed, 12 Jun 2019 02:34:43 GMT

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

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Wed, 12 Jun 2019 02:41:05 GMT

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

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

Reeza — Wed, 12 Jun 2019 03:24:47 GMT

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.

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Wed, 12 Jun 2019 04:02:18 GMT

@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

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Wed, 12 Jun 2019 04:07:38 GMT

@Reeza

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

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Wed, 12 Jun 2019 13:00:32 GMT

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;

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

novinosrin — Wed, 12 Jun 2019 13:04:34 GMT

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

Re: QUANTITY AND CALCULATE BALANCE AND CASE PACK

niloya — Wed, 12 Jun 2019 13:14:25 GMT

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