topic Re: Summary of a variable by ID in SAS Data Management

Summary of a variable by ID

Ardaneth — Mon, 30 Nov 2015 17:13:09 GMT

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!

Re: Summary of a variable by ID

PaigeMiller — Mon, 30 Nov 2015 17:34:24 GMT

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

Re: Summary of a variable by ID

Ardaneth — Tue, 01 Dec 2015 15:23:29 GMT

Neat!

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

Thanks Mr./Mrs. Miller!

Re: Summary of a variable by ID

Reeza — Tue, 01 Dec 2015 01:42:48 GMT

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.

Re: Summary of a variable by ID

gfarkas — Thu, 24 Dec 2015 20:54:39 GMT

Don't you need a RETAIN statement there?