SAS - Combine like values within rows, then add new variable for non like value(s)

1
votes

I have a large dataset and am trying to run an analyses on each customer (same account and routing #), which have 100's of transactions within the dataset. I was able to add SEQ # for like acct#'s and routing #s. How would I run an analyses to say SEQ #1 and give total # of deposits (Amount), max, min of deposits and potentially some other helpful data.

+-----------+--------+---------+--------+
| Routing#  | Acct#  | AMOUNT  | TOTAL  |SEQ #
+-----------+--------+---------+--------+
|      518  |     0  | 490.50  | 3777.5 | 1
|      518  |     0  | 170.00  | 3777.5 | 1
|      518  |     0  | 3117.00 | 3777.5 | 1
|      518  |    99  | 875.00  | 875    | 2
|      518  |   999  | 499.00  | 499    | 3
|       519 |      2 | 100.00  | 200.00 | 4
|       519 |      2 | 100.00  | 200.00 | 4
+-----------+--------+---------+--------+

Thanks

1
sas

1 Answers

0
votes

There are multiple ways to do this, but here is a data step way

data have;
input Routing Acct AMOUNT;
datalines;
518 0 490.50
518 0 170.00
518 0 3117.00
518 99 875.00
518 999 499.00
519 2 100.00
519 2 100.00
;

data want;
    do until (last.Acct);
        set have;
        by Routing Acct notsorted;
        total+amount;
    end;
    seq+1;
    do until (last.Acct);
        set have;
        by Routing Acct notsorted;
        output; 
    end;
    total=0;
run;