SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Summary of a variable by ID

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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
Trusted Advisor
Posts: 1,931

Re: Summary of a variable by ID

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

View solution in original post


All Replies
Solution
‎11-30-2015 12:40 PM
Trusted Advisor
Posts: 1,931

Re: Summary of a variable by ID

data count2;
    set count;
    by id;
    if first.id then count_sum=0;
    count_sum+count;
run;
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: 19,822

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
  • 460 views
  • 3 likes
  • 4 in conversation