topic Re: SAS Query in SAS Programming

SAS Query

scb — Tue, 07 Jan 2020 10:53:00 GMT

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

Re: SAS Query

PeterClemmensen — Tue, 07 Jan 2020 10:37:22 GMT

How come final=0 for ID=113?

Re: SAS Query

scb — Tue, 07 Jan 2020 10:49:44 GMT

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

Re: SAS Query

PeterClemmensen — Tue, 07 Jan 2020 11:21:32 GMT

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;