- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 02-11-2020 12:58 PM
(3086 views)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;