This is my data (below). The ID is not unique, so I want to combine the repeated observations into one. In the example there are just 2 variables (revenues and assets). My dataset has 50. I want to combine by choosing the either the average of both values, or the max of the two values. The goal is to have one observation for each ID. How can I efficiently do this for all 50 variables?
data have;
input Obs ID $12. code $ revenues $10. assets $10. ;
cards;
1 AC0000037163 C1 3500 6000
2 AC0000037163 C1 3470 6020
3 BE0000037282 U2 9350 21930
4 BE0000037282 U2 13001 18549
5 CZ0000037693 C1 32010 45010
6 CZ0000037693 C2 27981 59082
7 FR0000037738 U1 1237 4392
8 FR0000037738 U1 11000 24800
;
proc summary data=have nway;
class id;
var revenues--last_variable_name;
id code;
output out=want mean=;
run;
This computes the mean for each variable. If you want the max for each variable, change mean= to max=
proc summary data=have nway;
class id;
var revenues--last_variable_name;
id code;
output out=want mean=;
run;
This computes the mean for each variable. If you want the max for each variable, change mean= to max=
If ignoring Code.
proc means data=sashelp.cars nway noprint;
class origin;
output out=want mean(mpg_city mpg_highway) = max(cylinders msrp) = ;
run;
proc print data=want;
run;
List variables in Mean/max as desired for each statistic.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.