cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
srikanthyadav44
Quartz | Level 8 srikanthyadav44
Quartz | Level 8
Go to Solution

how to find the sum of first three largest shareholdings ?

Posted 05-02-2022 07:46 AM (1778 views)

dear all,

i have the data of in the following format 

company_nameyearpercent_shares
20 Microns Ltd.20100.07
20 Microns Ltd.20100.77
20 Microns Ltd.20102.5
20 Microns Ltd.20100.57
20 Microns Ltd.20100.35
20 Microns Ltd.20101.95
20 Microns Ltd.20101.85
20 Microns Ltd.20100.1
20 Microns Ltd.20100.5
20 Microns Ltd.201010.76
20 Microns Ltd.20100.37
20 Microns Ltd.20100.01
20 Microns Ltd.20109.26
20 Microns Ltd.20100.61
20 Microns Ltd.20100.13
20 Microns Ltd.20100.14
20 Microns Ltd.20100.08
20 Microns Ltd.20100.12
20 Microns Ltd.20100.03
20 Microns Ltd.20101.65
20 Microns Ltd.20100.21
20 Microns Ltd.20100.03
20 Microns Ltd.20100.04
20 Microns Ltd.20100.03
20 Microns Ltd.20101.95
20 Microns Ltd.20100.1
20 Microns Ltd.20100
20 Microns Ltd.20106.81
20 Microns Ltd.20101.25
20 Microns Ltd.20100.58
20 Microns Ltd.20100.18
20 Microns Ltd.20100.18
20 Microns Ltd.20101.05
20 Microns Ltd.20100.36
20 Microns Ltd.20101.39
20 Microns Ltd.20100.07
20 Microns Ltd.20100.64
20 Microns Ltd.20100.07
20 Microns Ltd.20100.33
20 Microns Ltd.20100.17
20 Microns Ltd.201018.67
20 Microns Ltd.20101.65
20 Microns Ltd.20100.77
20 Microns Ltd.20102.5
20 Microns Ltd.20100.57
20 Microns Ltd.20100.35
20 Microns Ltd.20101.95
20 Microns Ltd.20101.85
20 Microns Ltd.201010.8
20 Microns Ltd.201018.94
20 Microns Ltd.20100.14
20 Microns Ltd.20100.12
20 Microns Ltd.20100.08
20 Microns Ltd.20100.12
20 Microns Ltd.20100.21
20 Microns Ltd.20101.95
20 Microns Ltd.20106.81
20 Microns Ltd.20100.06
20 Microns Ltd.20101.19
20 Microns Ltd.20100.58
20 Microns Ltd.20101.05
20 Microns Ltd.20101.39
20 Microns Ltd.20100.33
3I Infotech Ltd.20102.73
3I Infotech Ltd.20101.39
3I Infotech Ltd.20101.59
3I Infotech Ltd.20108.12
3I Infotech Ltd.20102.57
3I Infotech Ltd.20101.75
3I Infotech Ltd.20102.07
3I Infotech Ltd.20103.5
3I Infotech Ltd.20101.9
3I Infotech Ltd.20102.07
3I Infotech Ltd.201023.13
3M India Ltd.20101.2
3M India Ltd.20101.93
3M India Ltd.20101.48
3M India Ltd.20102.9
3M India Ltd.20101.93
3M India Ltd.201076
3P Land Holdings Ltd.20104.87
3P Land Holdings Ltd.20101.2
3P Land Holdings Ltd.20105.56
3P Land Holdings Ltd.20102.11
3P Land Holdings Ltd.20104.58
3P Land Holdings Ltd.20102.24
3P Land Holdings Ltd.20100.34
3P Land Holdings Ltd.20102.74
3P Land Holdings Ltd.20103.47
3P Land Holdings Ltd.20104.59
3P Land Holdings Ltd.201021.92
3P Land Holdings Ltd.20102.04
3P Land Holdings Ltd.20104.65
3P Land Holdings Ltd.20103.58
3P Land Holdings Ltd.201013.94

it represents the company name, year and shareholding percentage.  it the panel data of nearly 5000 companies from 2010 to 2019. 

i have to find out the sum of first three largest shareholdings for each company for every year from 2010 to 2019. 

my output should have the following information 

company_nameyearsum of three largest shareholdigns 
   
   
   
   
   
   

 

please suggest  the appropriate SAS CODE 

 

thanks in advance

0 Likes
Reply
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: how to find the sum of first three largest shareholdings ?

Posted 05-02-2022 07:52 AM (1763 views)  |   In reply to srikanthyadav44

Compute the sum for ALL companies using PROC SUMMARY, then sort so the largest are first, then take the first three each year.

 

proc summary data=have nway;
    class company_name year;
    var percent_shares;
    output out=_sums_ sum=;
run;
proc sort data=_sums_;
    by year descending percent_shares;
run;

 

--
Paige Miller

View solution in original post

0 Likes
Reply
6 REPLIES 6
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: how to find the sum of first three largest shareholdings ?

Posted 05-02-2022 07:52 AM (1764 views)  |   In reply to srikanthyadav44

Compute the sum for ALL companies using PROC SUMMARY, then sort so the largest are first, then take the first three each year.

 

proc summary data=have nway;
    class company_name year;
    var percent_shares;
    output out=_sums_ sum=;
run;
proc sort data=_sums_;
    by year descending percent_shares;
run;

 

--
Paige Miller
0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: how to find the sum of first three largest shareholdings ?

Posted 05-02-2022 07:57 AM (1749 views)  |   In reply to srikanthyadav44

And then I thought of a better approach

 

proc summary data=have nway;
    class year company_name;
    var percent_shares;
    output out=_sums_ sum=;
run;
proc rank data=_sums_ descending out=ranks;
    by year;
    var percent_shares;
    ranks percent_shares_rank;
run;

 

The output of PROC RANK, variable named PERCENT_SHARES_RANK has values 1 2 3 for the top 3 each year.

--
Paige Miller
Reply
s_lassen
Meteorite | Level 14 s_lassen
Meteorite | Level 14

Re: how to find the sum of first three largest shareholdings ?

Posted 05-02-2022 08:06 AM (1737 views)  |   In reply to srikanthyadav44

You have a much better chance of getting an answer if you present your data as data step code, like this:

 

data have;
length company_name	$40 year 8 percent_shares 8;
infile cards dsd delimiter=',';
input company_name year percent_shares;
cards;
20 Microns Ltd.,2010,0.07
20 Microns Ltd.,2010,0.77
20 Microns Ltd.,2010,2.5
20 Microns Ltd.,2010,0.57
20 Microns Ltd.,2010,0.35
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,1.85
20 Microns Ltd.,2010,0.1
20 Microns Ltd.,2010,0.5
20 Microns Ltd.,2010,10.76
20 Microns Ltd.,2010,0.37
20 Microns Ltd.,2010,0.01
20 Microns Ltd.,2010,9.26
20 Microns Ltd.,2010,0.61
20 Microns Ltd.,2010,0.13
20 Microns Ltd.,2010,0.14
20 Microns Ltd.,2010,0.08
20 Microns Ltd.,2010,0.12
20 Microns Ltd.,2010,0.03
20 Microns Ltd.,2010,1.65
20 Microns Ltd.,2010,0.21
20 Microns Ltd.,2010,0.03
20 Microns Ltd.,2010,0.04
20 Microns Ltd.,2010,0.03
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,0.1
20 Microns Ltd.,2010,0
20 Microns Ltd.,2010,6.81
20 Microns Ltd.,2010,1.25
20 Microns Ltd.,2010,0.58
20 Microns Ltd.,2010,0.18
20 Microns Ltd.,2010,0.18
20 Microns Ltd.,2010,1.05
20 Microns Ltd.,2010,0.36
20 Microns Ltd.,2010,1.39
20 Microns Ltd.,2010,0.07
20 Microns Ltd.,2010,0.64
20 Microns Ltd.,2010,0.07
20 Microns Ltd.,2010,0.33
20 Microns Ltd.,2010,0.17
20 Microns Ltd.,2010,18.67
20 Microns Ltd.,2010,1.65
20 Microns Ltd.,2010,0.77
20 Microns Ltd.,2010,2.5
20 Microns Ltd.,2010,0.57
20 Microns Ltd.,2010,0.35
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,1.85
20 Microns Ltd.,2010,10.8
20 Microns Ltd.,2010,18.94
20 Microns Ltd.,2010,0.14
20 Microns Ltd.,2010,0.12
20 Microns Ltd.,2010,0.08
20 Microns Ltd.,2010,0.12
20 Microns Ltd.,2010,0.21
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,6.81
20 Microns Ltd.,2010,0.06
20 Microns Ltd.,2010,1.19
20 Microns Ltd.,2010,0.58
20 Microns Ltd.,2010,1.05
20 Microns Ltd.,2010,1.39
20 Microns Ltd.,2010,0.33
3I Infotech Ltd.,2010,2.73
3I Infotech Ltd.,2010,1.39
3I Infotech Ltd.,2010,1.59
3I Infotech Ltd.,2010,8.12
3I Infotech Ltd.,2010,2.57
3I Infotech Ltd.,2010,1.75
3I Infotech Ltd.,2010,2.07
3I Infotech Ltd.,2010,3.5
3I Infotech Ltd.,2010,1.9
3I Infotech Ltd.,2010,2.07
3I Infotech Ltd.,2010,23.13
3M India Ltd.,2010,1.2
3M India Ltd.,2010,1.93
3M India Ltd.,2010,1.48
3M India Ltd.,2010,2.9
3M India Ltd.,2010,1.93
3M India Ltd.,2010,76
3P Land Holdings Ltd.,2010,4.87
3P Land Holdings Ltd.,2010,1.2
3P Land Holdings Ltd.,2010,5.56
3P Land Holdings Ltd.,2010,2.11
3P Land Holdings Ltd.,2010,4.58
3P Land Holdings Ltd.,2010,2.24
3P Land Holdings Ltd.,2010,0.34
3P Land Holdings Ltd.,2010,2.74
3P Land Holdings Ltd.,2010,3.47
3P Land Holdings Ltd.,2010,4.59
3P Land Holdings Ltd.,2010,21.92
3P Land Holdings Ltd.,2010,2.04
3P Land Holdings Ltd.,2010,4.65
3P Land Holdings Ltd.,2010,3.58
3P Land Holdings Ltd.,2010,13.94
;run;

 

 

Here is a solution:

 

proc sort;
  by company_name year descending percent_shares;
run;

data want;
  Three_largest=0;
  do _N_=1 by 1 until(last.year);
    set have;
    by company_name year;
    if _N_<=3 then
      Three_largest+percent_shares;
    end;
    drop percent_shares;
run;

 

 

Reply
mkeintz
PROC Star mkeintz
PROC Star

Re: how to find the sum of first three largest shareholdings ?

Posted 05-02-2022 08:57 AM (1710 views)  |   In reply to srikanthyadav44

If your data are already grouped by company name and year, then you could keep an updated array of the 3 largest percentages in a data step:  At the end of the year calculate the sum:

 

data have;
length company_name	$40 year 8 percent_shares 8;
infile cards dsd delimiter=',';
input company_name year percent_shares;
cards;
20 Microns Ltd.,2010,0.07
20 Microns Ltd.,2010,0.77
20 Microns Ltd.,2010,2.5
20 Microns Ltd.,2010,0.57
20 Microns Ltd.,2010,0.35
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,1.85
20 Microns Ltd.,2010,0.1
20 Microns Ltd.,2010,0.5
20 Microns Ltd.,2010,10.76
20 Microns Ltd.,2010,0.37
20 Microns Ltd.,2010,0.01
20 Microns Ltd.,2010,9.26
20 Microns Ltd.,2010,0.61
20 Microns Ltd.,2010,0.13
20 Microns Ltd.,2010,0.14
20 Microns Ltd.,2010,0.08
20 Microns Ltd.,2010,0.12
20 Microns Ltd.,2010,0.03
20 Microns Ltd.,2010,1.65
20 Microns Ltd.,2010,0.21
20 Microns Ltd.,2010,0.03
20 Microns Ltd.,2010,0.04
20 Microns Ltd.,2010,0.03
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,0.1
20 Microns Ltd.,2010,0
20 Microns Ltd.,2010,6.81
20 Microns Ltd.,2010,1.25
20 Microns Ltd.,2010,0.58
20 Microns Ltd.,2010,0.18
20 Microns Ltd.,2010,0.18
20 Microns Ltd.,2010,1.05
20 Microns Ltd.,2010,0.36
20 Microns Ltd.,2010,1.39
20 Microns Ltd.,2010,0.07
20 Microns Ltd.,2010,0.64
20 Microns Ltd.,2010,0.07
20 Microns Ltd.,2010,0.33
20 Microns Ltd.,2010,0.17
20 Microns Ltd.,2010,18.67
20 Microns Ltd.,2010,1.65
20 Microns Ltd.,2010,0.77
20 Microns Ltd.,2010,2.5
20 Microns Ltd.,2010,0.57
20 Microns Ltd.,2010,0.35
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,1.85
20 Microns Ltd.,2010,10.8
20 Microns Ltd.,2010,18.94
20 Microns Ltd.,2010,0.14
20 Microns Ltd.,2010,0.12
20 Microns Ltd.,2010,0.08
20 Microns Ltd.,2010,0.12
20 Microns Ltd.,2010,0.21
20 Microns Ltd.,2010,1.95
20 Microns Ltd.,2010,6.81
20 Microns Ltd.,2010,0.06
20 Microns Ltd.,2010,1.19
20 Microns Ltd.,2010,0.58
20 Microns Ltd.,2010,1.05
20 Microns Ltd.,2010,1.39
20 Microns Ltd.,2010,0.33
3I Infotech Ltd.,2010,2.73
3I Infotech Ltd.,2010,1.39
3I Infotech Ltd.,2010,1.59
3I Infotech Ltd.,2010,8.12
3I Infotech Ltd.,2010,2.57
3I Infotech Ltd.,2010,1.75
3I Infotech Ltd.,2010,2.07
3I Infotech Ltd.,2010,3.5
3I Infotech Ltd.,2010,1.9
3I Infotech Ltd.,2010,2.07
3I Infotech Ltd.,2010,23.13
3M India Ltd.,2010,1.2
3M India Ltd.,2010,1.93
3M India Ltd.,2010,1.48
3M India Ltd.,2010,2.9
3M India Ltd.,2010,1.93
3M India Ltd.,2010,76
3P Land Holdings Ltd.,2010,4.87
3P Land Holdings Ltd.,2010,1.2
3P Land Holdings Ltd.,2010,5.56
3P Land Holdings Ltd.,2010,2.11
3P Land Holdings Ltd.,2010,4.58
3P Land Holdings Ltd.,2010,2.24
3P Land Holdings Ltd.,2010,0.34
3P Land Holdings Ltd.,2010,2.74
3P Land Holdings Ltd.,2010,3.47
3P Land Holdings Ltd.,2010,4.59
3P Land Holdings Ltd.,2010,21.92
3P Land Holdings Ltd.,2010,2.04
3P Land Holdings Ltd.,2010,4.65
3P Land Holdings Ltd.,2010,3.58
3P Land Holdings Ltd.,2010,13.94
;run;

data want (drop=_: percent_shares);
  set have;
  by company_name year notsorted;

  array pct {0:3}  _newpct _pct1-_pct3;
  retain _pct: ;

  if first.year then call missing(of _pct:);
  if percent_shares>_pct1 then do;
    _newpct=percent_shares;
    call sortn(of pct{*});
  end;
  if last.year;
  sum_of_3_largest=sum(of _pct:);
run;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reply
andreas_lds
Jade | Level 19 andreas_lds
Jade | Level 19

Re: how to find the sum of first three largest shareholdings ?

Posted 05-03-2022 07:45 AM (1651 views)  |   In reply to srikanthyadav44

In future posts, please post data in usable form, as shown by @s_lassen .

 

Proc Summary can do most of the task, unfortunately there seems to be no way avoiding the transpose that takes place:

proc summary data=have nway;
   class company_name year;
   var percent_shares;
   output out=top3(drop= _:) idgroup(max(percent_shares) out[3] (percent_shares)=) / autolabel autoname;
run;

data want;
   set top3;
   sum_top3 = sum(of percent_shares:);
   drop percent_shares:;
run;
Reply

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • ‎05-02-2022 07:46 AM
  • 1779 views
  • 4 likes
  • 5 in conversation