## How to select and keep the highest variable?

Solved
Occasional Contributor
Posts: 12

# How to select and keep the highest variable?

Hey, Guys,

I am really new to SAS, and trying to learn. But now I got stuck with this problem. Hopefully you can help me out of it.

For each stock, I get holding shares from different funds (holding funds numbers may vary). Now I have summed all the holdings of all funds, but now I want to calculate the holding shares of top one/two/three/whatever funds in terms of the shares they hold.

Any idea how to realize this?

Laoban

Thanks, guys, the data is quite big, so I will just post some man-made data. I think this will be enough.

Stock    Fund    Share

A           alpha      60

A           beta        33

A           gamma   12

B           alpha      88

B           gamma   90

C           ita         13

So sorry for my poor English, so basically what I want is.

I want to get the holding shares of the top one fund holding for a specific stock: A - 60, B - 90, C - 13.

or the top two funds' holding: A - 93, B - 178, C - 13 or NA for this case.

----------------------------------------------------------------

Thank you all. I somehow get some idea how to do that. But still I cannot modify your answers to my final goal.

My data set is like:

year  month  stock   fund   share

1988    3       A           alpha      10

1988    3      A           beta        33

1988    3      A           gamma   12

1988    3      B           alpha      28

1988    3      B           gamma   90

1988    3      C           ita         13

1988    6       A           alpha      60

1988    6      A           beta        53

1988    6      A           gamma   13

1988    6      B           alpha      86

1988    6      B           gamma   90

1988    3      C           ita         13

so my final output desired is like:

year  month  stock   first-fund    first-fund-share    second-fund   second-fund-share     first-two-fund-share

1988    3        A          beta            33                       gamma               12                            45

.....

.....

.....

I used the following code:

proc means data=raw noprint;

class stock   year  month;

types () stock   year  month;

var SHARES;

output out=top10list(rename=(_freq_=Numberfunds))sum= mean=

idgroup( max(SHARES) out[10] (SHARES FUNDNO

stock   year  month)=)/autolabel autoname;

run;

But it does not give me the fund holding for different years and months separately.

Unhappy...-^-

Accepted Solutions
Solution
‎06-23-2015 11:00 AM
Super User
Posts: 10,770

## Re: How to select and keep the highest variable?

### Code: Program

`data one;input year  month  stock  \$ fund \$  share;cards;1988 3 A alpha 101988 3 A beta 331988 3 A gamma 121988 3 B alpha 281988 3 B gamma 901988 3 C ita 131988 6 A alpha 601988 6 A beta 531988 6 A gamma 131988 6 B alpha 861988 6 B gamma 901988 3 C ita 13;run;proc sql; select max(count) into : n  from (   select year,month,stock,count(*) as count   from one    group by year,month,stock );quit;proc summary data=one nway; class year month stock; output out=want idgroup(out[&n] (fund share)=);run;`

All Replies
Super User
Posts: 23,689

## Re: How to select and keep the highest variable?

Can you please post some sample data and the expected output

Occasional Contributor
Posts: 12

Super User
Posts: 13,517

## Re: How to select and keep the highest variable?

Since there are probably several ways to interpret your requirement it would be best for you to provide some data that shows what you currently have and what the output(s) for that example data should be. It need not be real data as long as it shows the structure of your current data and hopefully clear rules of how the output is generated.

Occasional Contributor
Posts: 12

Super User
Posts: 23,689

## Re: How to select and keep the highest variable?

I'm having a hard time interpreting your question, especially without sample output.

Is it:

For each Fund, show the top share holdings

For each Share, Show the top fund

Something else???

Frequent Contributor
Posts: 132

## Re: How to select and keep the highest variable?

You could use a SQL procedure or a MEANS procedure for a basic way to get you the top Share per Stock:

proc sql;

create table want as

select Stock, max(Share) as Share_Max

from have

group by Stock

order by Stock;

quit;

proc means data=have max nway noprint;

var Share;

class Stock;

output out=want (drop=_FREQ_ _TYPE_)

max=Share_Max;

run;

Hope that helps!

Frequent Contributor
Posts: 102

## Re: How to select and keep the highest variable?

data one;

input stock \$2. fund :\$8.  share;

cards;

A           alpha      60

A           beta        33

A           gamma     12

B           alpha      88

B           gamma      90

C           ita         13

proc sort;  by stock descending share ;

proc print;  run;

data two;   set one;  by stock;

if first.stock then output;

title "top stock";

proc print;   run;

data three; set one; by stock;

if first.stock then do; n=0; top2stks=0; end;

n+1;  top2stks+share;

if last.stock and n=1 then do; top2stks=.; output; end;

if n=2 then output;

drop share n;

title "top 2 stocks";  proc print;  run;

Jim

Super User
Posts: 10,770

## Re: How to select and keep the highest variable?

If there were some duplicated value for SHARE?

### Code: Program

`data one;  input stock \$2. fund :\$8.  share;cards;A alpha 60A beta 33A gamma 12B alpha 88B gamma 90C ita 13;run;proc sort data=one; by stock descending share;run;data top_one; set one; by stock; if first.stock;run;data top_two; set one; by stock; if first.stock then do;sum=0;n=0;end; n+1; sum+share; if n eq 2 then output; run;`

Xia Keshan

Occasional Contributor
Posts: 12

## Re: How to select and keep the highest variable?

Thank you all. I somehow get some idea how to do that. But still I cannot modify your answers to my final goal.

My data set is like:

year  month  stock   fund   share

1988    3       A           alpha      10

1988    3      A           beta        33

1988    3      A           gamma   12

1988    3      B           alpha      28

1988    3      B           gamma   90

1988    3      C           ita         13

1988    6       A           alpha      60

1988    6      A           beta        53

1988    6      A           gamma   13

1988    6      B           alpha      86

1988    6      B           gamma   90

1988    3      C           ita         13

so my final output desired is like:

year  month  stock   first-fund    first-fund-share    second-fund   second-fund-share     first-two-fund-share

1988    3        A          beta            33                       gamma               12                            45

.....

.....

.....

I used the following code:

proc means data=raw noprint;

class stock   year  month;

types () stock   year  month;

var SHARES;

output out=top10list(rename=(_freq_=Numberfunds))sum= mean=

idgroup( max(SHARES) out[10] (SHARES FUNDNO

stock   year  month)=)/autolabel autoname;

run;

But it does not give me the fund holding for different years and months separately.

Unhappy...-^-

Solution
‎06-23-2015 11:00 AM
Super User
Posts: 10,770

## Re: How to select and keep the highest variable?

### Code: Program

`data one;input year  month  stock  \$ fund \$  share;cards;1988 3 A alpha 101988 3 A beta 331988 3 A gamma 121988 3 B alpha 281988 3 B gamma 901988 3 C ita 131988 6 A alpha 601988 6 A beta 531988 6 A gamma 131988 6 B alpha 861988 6 B gamma 901988 3 C ita 13;run;proc sql; select max(count) into : n  from (   select year,month,stock,count(*) as count   from one    group by year,month,stock );quit;proc summary data=one nway; class year month stock; output out=want idgroup(out[&n] (fund share)=);run;`
Super User
Posts: 10,770

## Re: How to select and keep the highest variable?

`data one;  input stock \$2. fund :\$8.  share;cards;A alpha 60A beta 33A gamma 12B alpha 88B gamma 90C ita 13;run;ods select ExtremeObs;ods output ExtremeObs=Top_two(keep=stock high);proc univariate data=one nextrobs=2;by stock;var share;run;proc summary data=Top_two;by stock;var high;output out=Top_two_sum sum=;run;`