Sum by group and count variable

[marleeakerson]

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!

[PaigeMiller]

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;

[ed_sas_member]

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;