How to select and keep the highest variable?

[Laoban_James]

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?

Thx in advance.

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...-^-

[Reeza]

Can you please post some sample data and the expected output

[Laoban_James]

updated already. Thx in advance.

[ballardw]

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.

[Laoban_James]

updated already. Thx in advance.

[Reeza]

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???

[dcruik]

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!

[Jim_G]

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

[Ksharp]

If there were some duplicated value for SHARE?

Code: Program

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
;
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

[Laoban_James]

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...-^-

[Ksharp · Accepted solution]

Code: Program

data one;
input year  month  stock  $ fund $  share;
cards;
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
;
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;

[Ksharp]

Code: Program

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
;
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;