BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Satori
Quartz | Level 8

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
;

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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=

--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26
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=

--
Paige Miller
Reeza
Super User
Please show sample output. How do you want the 5/6th observations handled where the CODE is not the same?

If you can ignore the code, then look into PROC MEANS to summarize the data.
Reeza
Super User

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. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 424 views
  • 0 likes
  • 3 in conversation