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...-^-
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;
Can you please post some sample data and the expected output
updated already. Thx in advance.
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.
updated already. Thx in advance.
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???
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!
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
If there were some duplicated value for SHARE?
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
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...-^-
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.