Convert quantity to case pack

[hcbn]

How to convert the quantities to the case pack

	Input
Date	dest_id	qty	Case_Qty
03Mar2019	12305	0	12
04Mar2019	12305	0	0
05Mar2019	12305	0	0
06Mar2019	12305	0	0
07Mar2019	12305	0	0
08Mar2019	12305	5	0
09Mar2019	12305	0	0
10Mar2019	12305	0	0
11Mar2019	12305	0	0
12Mar2019	12305	0	0
13Mar2019	12305	0	0
14Mar2019	12305	0	0
15Mar2019	12305	6	0
16Mar2019	12305	0	0
17Mar2019	12305	0	0
18Mar2019	12305	0	0
19Mar2019	12305	0	0
20Mar2019	12305	0	0
21Mar2019	12305	0	0
22Mar2019	12305	4	12
23Mar2019	12305	0	0
03Mar2019	12913	0	12
04Mar2019	12913	0	0
05Mar2019	12913	2	0
06Mar2019	12913	0	0
07Mar2019	12913	0	0
08Mar2019	12913	1	0
09Mar2019	12913	0	0
10Mar2019	12913	0	0
11Mar2019	12913	0	0
12Mar2019	12913	4	0
13Mar2019	12913	0	0
14Mar2019	12913	0	0
15Mar2019	12913	0	0
16Mar2019	12913	0	0
17Mar2019	12913	0	0
18Mar2019	12913	0	0
19Mar2019	12913	2	0
20Mar2019	12913	0	0
21Mar2019	12913	0	0
22Mar2019	12913	5	12
23Mar2019	12913	0	0

	Output
Date	dest_id	qty	Case_pack
03Mar2019	12305	0	0
04Mar2019	12305	0	0
05Mar2019	12305	0	0
06Mar2019	12305	0	0
07Mar2019	12305	0	0
08Mar2019	12305	5	12
09Mar2019	12305	0	0
10Mar2019	12305	0	0
11Mar2019	12305	0	0
12Mar2019	12305	0	0
13Mar2019	12305	0	0
14Mar2019	12305	0	0
15Mar2019	12305	6	0
16Mar2019	12305	0	0
17Mar2019	12305	0	0
18Mar2019	12305	0	0
19Mar2019	12305	0	0
20Mar2019	12305	0	0
21Mar2019	12305	0	0
22Mar2019	12305	4	12
23Mar2019	12305	0	0
03Mar2019	12913	0	0
04Mar2019	12913	0	0
05Mar2019	12913	2	12
06Mar2019	12913	0	0
07Mar2019	12913	0	0
08Mar2019	12913	1	0
09Mar2019	12913	0	0
10Mar2019	12913	0	0
11Mar2019	12913	0	0
12Mar2019	12913	4	0
13Mar2019	12913	0	0
14Mar2019	12913	0	0
15Mar2019	12913	0	0
16Mar2019	12913	0	0
17Mar2019	12913	0	0
18Mar2019	12913	0	0
19Mar2019	12913	2	0
20Mar2019	12913	0	0
21Mar2019	12913	0	0
22Mar2019	12913	5	12
23Mar2019	12913	0	0

[Amir]

Please explain all the rules to convert case_qty to case_pack.

[PGStats · Accepted solution]

You seem to be doing this?

data have;
input Date $ dest_id qty Case_Qty;
datalines;
03-mars-19	12305	0	12
04-mars-19	12305	0	0
05-mars-19	12305	0	0
06-mars-19	12305	0	0
07-mars-19	12305	0	0
08-mars-19	12305	5	0
09-mars-19	12305	0	0
10-mars-19	12305	0	0
11-mars-19	12305	0	0
12-mars-19	12305	0	0
13-mars-19	12305	0	0
14-mars-19	12305	0	0
15-mars-19	12305	6	0
16-mars-19	12305	0	0
17-mars-19	12305	0	0
18-mars-19	12305	0	0
19-mars-19	12305	0	0
20-mars-19	12305	0	0
21-mars-19	12305	0	0
22-mars-19	12305	4	12
23-mars-19	12305	0	0
03-mars-19	12913	0	12
04-mars-19	12913	0	0
05-mars-19	12913	2	0
06-mars-19	12913	0	0
07-mars-19	12913	0	0
08-mars-19	12913	1	0
09-mars-19	12913	0	0
10-mars-19	12913	0	0
11-mars-19	12913	0	0
12-mars-19	12913	4	0
13-mars-19	12913	0	0
14-mars-19	12913	0	0
15-mars-19	12913	0	0
16-mars-19	12913	0	0
17-mars-19	12913	0	0
18-mars-19	12913	0	0
19-mars-19	12913	2	0
20-mars-19	12913	0	0
21-mars-19	12913	0	0
22-mars-19	12913	5	12
23-mars-19	12913	0	0
;

data want;
do until(last.dest_id);
    set have; by dest_id;
    if qty = 0 and case_qty > 0 then do;
        case_hold = sum(case_hold, case_qty);
        case_pack = 0;
        end;
    else if qty > 0 and case_qty = 0 then do;
        case_pack = sum(case_qty, case_hold);
        case_hold = 0;
        end;
    else case_pack = case_qty;
    output;
    end;
drop case_hold case_qty;
run;

If not, please elaborate.