topic Re: Sum up rows with same id in SAS Programming

Sum up rows with same id

MarySa — Tue, 29 Sep 2015 21:41:46 GMT

Hi all,

I have a dataset wich looks like this;

id quarter1 quarter2 quarter3

1 1 0 0

1 0 1 0

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!!

Re: Sum up rows with same id

ballardw — Thu, 03 Jan 2019 16:02:10 GMT

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.

And from @RW9:

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;

Re: Sum up rows with same id

RW9 — Wed, 30 Sep 2015 08:51:32 GMT

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;

Re: Sum up rows with same id

Steelers_In_DC — Wed, 30 Sep 2015 13:36:37 GMT

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;