New SAS User

Completely new to SAS or trying something new with SAS? Post here for help getting started.
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-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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
  • 3087 views
  • 1 like
  • 3 in conversation