topic Re: combine observations to have just one for each ID in SAS Programming

combine observations to have just one for each ID

Satori — Thu, 02 Mar 2023 18:50:33 GMT

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
;

Re: combine observations to have just one for each ID

PaigeMiller — Thu, 02 Mar 2023 18:58:31 GMT

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=

Re: combine observations to have just one for each ID

Reeza — Thu, 02 Mar 2023 18:54:56 GMT

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.

Re: combine observations to have just one for each ID

Reeza — Thu, 02 Mar 2023 19:01:35 GMT

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.