topic Re: Sum by group and count variable in New SAS User

Sum by group and count variable

marleeakerson — Tue, 11 Feb 2020 17:58:09 GMT

Hello,

I am trying to create sums by group and create a new variable that is a count of all the observations with the same ID number. See sample data set below:

ID X Y
1 10 15

1 7 19

1 13 11

2 10 15

2 11 17

3 35 21

3 12 8

3 11 7

I want the total aggregate sums of X and Y for each ID group, and a new variable that is a count of all the entries for each ID number (for example count for ID 1 would be 3, ID 2=2, ID 3= 3)

Thank you!

Re: Sum by group and count variable

PaigeMiller — Tue, 11 Feb 2020 18:08:45 GMT

This is a job for PROC SUMMARY

proc summary data=have nway;
    class id;
    var x y;
    output out=want sum=sum_x sum_y n(x)=count;
run;

Re: Sum by group and count variable

ed_sas_member — Tue, 11 Feb 2020 19:40:03 GMT

Hi @marleeakerson

You can use either PROC MEANS / SUMMARY or PROC SQL or even PROC TABULATE:

proc means data=have noprint;
	var x y;
	class id;
	ways 1;
	output out=want (drop=_:) n=count sum=x_sum y_sum;
run;

/* OR */ 

proc sql;
	create table want as
	select id, count(id) as count, sum(x) as x_sum, sum(y) as y_sum
	from have
	group by id;
run;

/* OR */ 

proc tabulate data=have out=want (drop=_:);
	var x y;
	class id;
	tables id, n x*sum y*sum ;
run;