Summary of a variable by ID

Solved
Occasional Contributor
Posts: 5

Summary of a variable by ID

[ Edited ]

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!

Accepted Solutions
Solution
‎11-30-2015 12:40 PM
Respected Advisor
Posts: 2,833

Re: Summary of a variable by ID

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

All Replies
Solution
‎11-30-2015 12:40 PM
Respected Advisor
Posts: 2,833

Re: Summary of a variable by ID

``````data count2;
set count;
by id;
if first.id then count_sum=0;
count_sum+count;
run;``````
--
Paige Miller
Occasional Contributor
Posts: 5

Re: Summary of a variable by ID

[ Edited ]
Posted in reply to PaigeMiller

Neat!

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

Thanks Mr./Mrs. Miller!

Contributor
Posts: 23

Re: Summary of a variable by ID

Posted in reply to PaigeMiller

Don't you need a RETAIN statement there?

Super User
Posts: 23,332

Re: Summary of a variable by ID

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 498 views
• 3 likes
• 4 in conversation