Help using Base SAS procedures

Sum by group while maintaining individual observations in dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Sum by group while maintaining individual observations in dataset

Hi,

I have a question of summing counts by group while maintaining them individually in the original dataset.

For example:
obs group count -> obs group sum_count
1   1        10           1    1        25
2   1        15           2    1        25
3   2         5            3    2         5

I know there is a way in sas to sum counts by group that gets:
group count
1        25
2        5    

But if I create a new dateset using this output and merge back with my original dateset, the combined dataset would only read up to ''n (group)" instead of my original "N(observations)'.

Is there any other ways of getting my original dateset to my goal?

I couldn't find a direct sas command for doing the equivalent sum_count stata did Smiley Sad

Thanks!!


Accepted Solutions
Solution
‎07-21-2015 08:58 AM
Frequent Contributor
Posts: 130

Re: Sum by group while maintaining individual observations in dataset

Try the following code using BY processing:

data have;

input obs group count;

datalines;

1 1 10

2 1 15

3 2 5

;

run;

data want;

do until (last.group);

     set have;

     by group;

     sum_count=sum(count,sum_count); end;

do until (last.group);

     set have;

     by group;

     output; end;

run;


The first do until sum's all counts within each by group and the last do until outputs all observations from the original data set with the sum_count field.

Hope this helps!

View solution in original post


All Replies
Solution
‎07-21-2015 08:58 AM
Frequent Contributor
Posts: 130

Re: Sum by group while maintaining individual observations in dataset

Try the following code using BY processing:

data have;

input obs group count;

datalines;

1 1 10

2 1 15

3 2 5

;

run;

data want;

do until (last.group);

     set have;

     by group;

     sum_count=sum(count,sum_count); end;

do until (last.group);

     set have;

     by group;

     output; end;

run;


The first do until sum's all counts within each by group and the last do until outputs all observations from the original data set with the sum_count field.

Hope this helps!

Super User
Super User
Posts: 6,500

Re: Sum by group while maintaining individual observations in dataset

PROC SQL let's you do that.

create table want as

select *,sum(count) as sum_count

from have

group by group

;

Contributor
Posts: 44

Re: Sum by group while maintaining individual observations in dataset

data have;

input obs group count ;

cards;

1   1        10 

2   1        15

3   2         5 

;

run;

proc sql;

create table want as

select group,

       count,

       sum(count) as N_group

from   have

group by group;

quit;

Occasional Contributor
Posts: 8

Re: Sum by group while maintaining individual observations in dataset

Thanks to you all. I really appreciate your information!

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 323 views
  • 0 likes
  • 4 in conversation