BookmarkSubscribeRSS Feed
marleeakerson
Calcite | Level 5

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!

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
ed_sas_member
Meteorite | Level 14

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;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2268 views
  • 1 like
  • 3 in conversation