Summary of a variable by ID

[Ardaneth]

Dear SAS-Community,

i have a question for you.

I have data in the structure you can see on the left side. I want it to be summarized by the ID, like you can see on the right side.

ID   COUNT          ID   COUNT_sum

1        3       ->      1         3

1        1       ->      1         4

1       12      ->      1        16

1        0       ->      1        16

1        2       ->      1        18

2        0       ->      2         0

2        4       ->      2         4

2        6       ->      2        10

3        2       ->      3         2

3        1       ->      3         3

Sadly the lag function doesnt work like id like it to, otherwise this should have been the solution:

data count2; set count;

    if ID ne lag1 (ID) then Count_sum=count;

run;

data count3; set count2;

    if ID = lag1 (ID) then Count_sum=lag1(Count_sum)+count;

run;

Like i said the lag function doesnt seem to work in an "if then" statement as i want it to, but it should get you an impression of what i want to do with my data. The dataset is pretty big and Spells for one ID can go up to 170. I would be glad about any suggestions how to handle this problem.

Best Wishes!

[PaigeMiller · Accepted solution]

data count2;
    set count;
    by id;
    if first.id then count_sum=0;
    count_sum+count;
run;

[Ardaneth]

Neat!

This is much more simple/elegant than i hoped for.

Thanks Mr./Mrs. Miller!

[gfarkas]

Don't you need a RETAIN statement there?

[Reeza]

Just a note that lag doesn't work in if/then statements. You can use IFN/IFC or you can calculate lag and then apply your IF/THEN condition.