topic Re: How to select and keep the highest variable? in SAS Procedures

How to select and keep the highest variable?

Laoban_James — Mon, 22 Jun 2015 18:58:04 GMT

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

Re: How to select and keep the highest variable?

Reeza — Mon, 22 Jun 2015 19:10:15 GMT

Can you please post some sample data and the expected output

Re: How to select and keep the highest variable?

ballardw — Mon, 22 Jun 2015 19:10:46 GMT

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.

Re: How to select and keep the highest variable?

Laoban_James — Mon, 22 Jun 2015 21:14:55 GMT

updated already. Thx in advance.

Re: How to select and keep the highest variable?

Laoban_James — Mon, 22 Jun 2015 21:15:06 GMT

updated already. Thx in advance.

Re: How to select and keep the highest variable?

Reeza — Mon, 22 Jun 2015 21:20:18 GMT

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

Re: How to select and keep the highest variable?

dcruik — Tue, 23 Jun 2015 12:55:01 GMT

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!

Re: How to select and keep the highest variable?

Jim_G — Tue, 23 Jun 2015 13:34:22 GMT

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

Re: How to select and keep the highest variable?

Ksharp — Tue, 23 Jun 2015 14:39:07 GMT

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

Re: How to select and keep the highest variable?

Laoban_James — Tue, 23 Jun 2015 14:46:08 GMT

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

Re: How to select and keep the highest variable?

Ksharp — Tue, 23 Jun 2015 14:46:33 GMT

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;

Re: How to select and keep the highest variable?

Ksharp — Tue, 23 Jun 2015 15:00:08 GMT

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;