SAS Query

[scb]

I have the below dataset, and both dataset is linked by ID.  I would like to deduct the penalty from the winning; and to get the final penalty balance for individual case.  Anyone can help? Many Thanks.

DATA PENALTY;
INPUT GAME $ CASE $ ID $ PENALTY;
CARDS;
CC C1 111 480
HL H1 111 1120
SI S1 111 800
CC C2 112 750
SI S2 113 829
;
RUN;

DATA WINNING;
INPUT ID $ WINNING;
CARDS;
111 3000
112 700
113 800
;
RUN;

Desired output:

Dataset Winning (Penalties variable is the aggregate of penalty variable for individual ID, and must not more than the "winning")

ID     Winning  Penalties  Final

111   3000       2400        600

112   700         700          0

113   800         800          0

Dataset Penalty (Balance variable is the balance penalties that have not been deducted from the winning)

GAME   CASE  ID      PENALTY   BALANCE
CC        C1       111   480             0
HL         H1       111   1120           0
SI          S1       111    800            0
CC        C2       112   750            50
SI          S2       113   829            29

[PeterClemmensen]

How come final=0 for ID=113?

[scb]

The penalties is 829, but winning is 800 only.  Penalties cannot more than winning, hence can only deduct max 800 for ID 113.

[PeterClemmensen]

One way

data want;
    format id winning Penalties Final;
    if _N_=1 then do;
        dcl hash h(ordered:'y');
        h.definekey('ID');
        h.definedata('Penalties');
        h.definedone();

        do until (lr);
            set penalty end=lr;
            if h.find() ne 0 then Penalties=penalty;
            else Penalties+penalty;
            h.replace();
        end;
    end;

    set winning;
    rc=h.find();

    if winning <= Penalties then Penalties=winning;
    Final=winning-Penalties;

    keep id winning Penalties Final;
run;