How to select the maximum of a cluster inside a variable?

[Phil_NZ]

Hi all SAS Users,

I have a dataset named ave_mar as below:

gviidkey	x1	    x2	    ave_mar_cap
001166_01W	0	    3	    781881399.22
001166_02W	0	    3	    782818024.77
001166_01   0       3       59988888.13
001491_01W	0	    3	    211021885.49
001855_03W	0	    3	    7490455.1517
001932_01W	0	    3	    6966150208.8
001945_01W	0	    3	    3661586156.1
002162_01W	0	    3	    2576720.9485
002162_02W	0	    3	    2163731.9025
002338_01W	0	    3	    772042538.92
002410_01W	0	    3	    79948092816
002411_01W	0	    3	    33749159713
002411_03W	0	    3	    84226468905

gviidkey is made by gvkey (must be 6 first character) and iid (either 3 or 2 last character). For example: gviidkey 001166_01w, in which 001166 is gvkey and 01w is iid. In case gviidkey= 001166_01, in which 034555 is gvkey and 01 is iid.

It can ben seen from the dataset above is there are some observations that the gviidkey are similar, what I want is select the gviidkey that its ave_mar_cap is highest  amongst all gviidkey of this gvkey.

Simplistically speaking, what I want as below:

gviidkey	x1	    x2	    ave_mar_cap
001166_02W	0	    3	    782818024.77
001491_01W	0	    3	    211021885.49
001855_03W	0	    3	    7490455.1517
001932_01W	0	    3	    6966150208.8
002162_01W	0	    3	    2576720.9485
002338_01W	0	    3	    772042538.92
002410_01W	0	    3	    79948092816
002411_03W	0	    3	    84226468905

If my explaining is not clear, please let me know.

My novice idea is: create a variable named gvkey, then do

proc means data=ave_mar;
  by gvkey;
 var ave_mar_cap;
output out=want max=selected;
run;

I am not sure it is correct or there is any other way to do so as well

Warm regards.

[FreelanceReinh · Accepted solution]

Hi @Phil_NZ,

With PROC SQL you can define the grouping variable as an expression (here: substr(gviidkey,1,6)), so you don't need to create a new variable in an intermediate step.

proc sql;
create table want as
select *
from have
group by substr(gviidkey,1,6)
having ave_mar_cap=max(ave_mar_cap);
quit;