Sum up rows with same id

Reply
New User
Posts: 1

Sum up rows with same id

Hi all,

 

I have a dataset wich looks like this;

 

id      quarter1       quarter2         quarter3

1            1                  0                    0

1            1                  0                    0

1            0                  1                    0

2            0                  0                    1

2            0                  0                    1

2            0                  0                    1

 

And I want to sum up each row based on the id so that output looks like this;

 

id         quarter1        quarter2          quarter3

1             2                     1                       0

2             0                     0                       3

 

 

Thank you!!

 

 

Super User
Posts: 11,343

Re: Sum up rows with same id

Proc summary data=have nway;

    class id;

    var var1 var2 var3;

    output out = want sum=;

run;

Will generate an output data set. There will be two additional variables _type_ and _freq_ that provide information about the combinations of class variables (_type_) and number of records used (_freq_).

 

 

Also Proc Report and Proc tabulate will generate tables with prettier output.

Super User
Super User
Posts: 7,955

Re: Sum up rows with same id

Also you could do:

proc sql;

  create table WANT as

  select ID, 

            sum(QUARTER1) as QUARTER1,

            sum(QUARTER2) as QUARTER2,

            sum(QUARTER3) as QUARTER3

  from   HAVE

  group by ID;

quit;

Valued Guide
Posts: 860

Re: Sum up rows with same id

Here is a solutions:

 

data have;
input id quarter1 quarter2 quarter3;
cards;
1            1                  0                    0
1            1                  0                    0
1            0                  1                    0
2            0                  0                    1
2            0                  0                    1
2            0                  0                    1
;

proc sql;
create table want as
select distinct
id,
sum(quarter1) as quarter1,
sum(quarter2) as quarter2,
sum(quarter3) as quarter3
from have
group by id;

Ask a Question
Discussion stats
  • 3 replies
  • 1253 views
  • 0 likes
  • 4 in conversation