dear all,
i have the data set in the following format
company_name | year | type | percent_shares |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.07 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.77 |
20 Microns Ltd. | 2010 | Locked-In Shares | 2.5 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.57 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.35 |
20 Microns Ltd. | 2010 | Locked-In Shares | 1.95 |
20 Microns Ltd. | 2010 | Locked-In Shares | 1.85 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.1 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.5 |
20 Microns Ltd. | 2010 | Locked-In Shares | 10.76 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.37 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.01 |
20 Microns Ltd. | 2010 | Locked-In Shares | 9.26 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.61 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.13 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.14 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.08 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.12 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.03 |
20 Microns Ltd. | 2010 | Locked-In Shares | 1.65 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.21 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.03 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.04 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.03 |
20 Microns Ltd. | 2010 | Locked-In Shares | 1.95 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.1 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0 |
20 Microns Ltd. | 2010 | Locked-In Shares | 6.81 |
20 Microns Ltd. | 2010 | Locked-In Shares | 1.25 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.58 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.18 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.18 |
20 Microns Ltd. | 2010 | Locked-In Shares | 1.05 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.36 |
20 Microns Ltd. | 2010 | Locked-In Shares | 1.39 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.07 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.64 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.07 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.33 |
20 Microns Ltd. | 2010 | Locked-In Shares | 0.17 |
20 Microns Ltd. | 2010 | Non-Promoters | 18.67 |
20 Microns Ltd. | 2010 | Non-Promoters | 1.65 |
20 Microns Ltd. | 2010 | Promoters | 0.77 |
20 Microns Ltd. | 2010 | Promoters | 2.5 |
20 Microns Ltd. | 2010 | Promoters | 0.57 |
20 Microns Ltd. | 2010 | Promoters | 0.35 |
20 Microns Ltd. | 2010 | Promoters | 1.95 |
20 Microns Ltd. | 2010 | Promoters | 1.85 |
20 Microns Ltd. | 2010 | Promoters | 10.8 |
20 Microns Ltd. | 2010 | Promoters | 18.94 |
20 Microns Ltd. | 2010 | Promoters | 0.14 |
20 Microns Ltd. | 2010 | Promoters | 0.12 |
20 Microns Ltd. | 2010 | Promoters | 0.08 |
20 Microns Ltd. | 2010 | Promoters | 0.12 |
20 Microns Ltd. | 2010 | Promoters | 0.21 |
20 Microns Ltd. | 2010 | Promoters | 1.95 |
20 Microns Ltd. | 2010 | Promoters | 6.81 |
20 Microns Ltd. | 2010 | Promoters | 0.06 |
20 Microns Ltd. | 2010 | Promoters | 1.19 |
20 Microns Ltd. | 2010 | Promoters | 0.58 |
20 Microns Ltd. | 2010 | Promoters | 1.05 |
20 Microns Ltd. | 2010 | Promoters | 1.39 |
20 Microns Ltd. | 2010 | Promoters | 0.33 |
i have to compute largest and second largest values of percent_shares for each 'company' for 'year' for each 'type' .
my output should be in the following format
company_name | year | type | percent_shares | first_largest | second_largest |
please suggest me a SAS code in this regard
thanking you in advance
Classic use of the IDGROUP Option in Proc Summary
proc summary data=have nway;
class company_name year type;
var percent_shares;
output out=want(drop=_:) idgroup (max(percent_shares) out[2] (percent_shares) = largest);
run;
how about this code?
data have;
length company_name $15 year 8 type $16 percent_shares 8;
input company_name $ 1-15 year type $ 22-37 percent_shares;
datalines;
20 Microns Ltd. 2010 Locked-In Shares 0.07
20 Microns Ltd. 2010 Locked-In Shares 0.77
20 Microns Ltd. 2010 Locked-In Shares 2.5
20 Microns Ltd. 2010 Locked-In Shares 0.57
20 Microns Ltd. 2010 Locked-In Shares 0.35
20 Microns Ltd. 2010 Locked-In Shares 1.95
20 Microns Ltd. 2010 Locked-In Shares 1.85
20 Microns Ltd. 2010 Locked-In Shares 0.1
20 Microns Ltd. 2010 Locked-In Shares 0.5
20 Microns Ltd. 2010 Locked-In Shares 10.76
20 Microns Ltd. 2010 Locked-In Shares 0.37
20 Microns Ltd. 2010 Locked-In Shares 0.01
20 Microns Ltd. 2010 Locked-In Shares 9.26
20 Microns Ltd. 2010 Locked-In Shares 0.61
20 Microns Ltd. 2010 Locked-In Shares 0.13
20 Microns Ltd. 2010 Locked-In Shares 0.14
20 Microns Ltd. 2010 Locked-In Shares 0.08
20 Microns Ltd. 2010 Locked-In Shares 0.12
20 Microns Ltd. 2010 Locked-In Shares 0.03
20 Microns Ltd. 2010 Locked-In Shares 1.65
20 Microns Ltd. 2010 Locked-In Shares 0.21
20 Microns Ltd. 2010 Locked-In Shares 0.03
20 Microns Ltd. 2010 Locked-In Shares 0.04
20 Microns Ltd. 2010 Locked-In Shares 0.03
20 Microns Ltd. 2010 Locked-In Shares 1.95
20 Microns Ltd. 2010 Locked-In Shares 0.1
20 Microns Ltd. 2010 Locked-In Shares 0
20 Microns Ltd. 2010 Locked-In Shares 6.81
20 Microns Ltd. 2010 Locked-In Shares 1.25
20 Microns Ltd. 2010 Locked-In Shares 0.58
20 Microns Ltd. 2010 Locked-In Shares 0.18
20 Microns Ltd. 2010 Locked-In Shares 0.18
20 Microns Ltd. 2010 Locked-In Shares 1.05
20 Microns Ltd. 2010 Locked-In Shares 0.36
20 Microns Ltd. 2010 Locked-In Shares 1.39
20 Microns Ltd. 2010 Locked-In Shares 0.07
20 Microns Ltd. 2010 Locked-In Shares 0.64
20 Microns Ltd. 2010 Locked-In Shares 0.07
20 Microns Ltd. 2010 Locked-In Shares 0.33
20 Microns Ltd. 2010 Locked-In Shares 0.17
20 Microns Ltd. 2010 Non-Promoters 18.67
20 Microns Ltd. 2010 Non-Promoters 1.65
20 Microns Ltd. 2010 Promoters 0.77
20 Microns Ltd. 2010 Promoters 2.5
20 Microns Ltd. 2010 Promoters 0.57
20 Microns Ltd. 2010 Promoters 0.35
20 Microns Ltd. 2010 Promoters 1.95
20 Microns Ltd. 2010 Promoters 1.85
20 Microns Ltd. 2010 Promoters 10.8
20 Microns Ltd. 2010 Promoters 18.94
20 Microns Ltd. 2010 Promoters 0.14
20 Microns Ltd. 2010 Promoters 0.12
20 Microns Ltd. 2010 Promoters 0.08
20 Microns Ltd. 2010 Promoters 0.12
20 Microns Ltd. 2010 Promoters 0.21
20 Microns Ltd. 2010 Promoters 1.95
20 Microns Ltd. 2010 Promoters 6.81
20 Microns Ltd. 2010 Promoters 0.06
20 Microns Ltd. 2010 Promoters 1.19
20 Microns Ltd. 2010 Promoters 0.58
20 Microns Ltd. 2010 Promoters 1.05
20 Microns Ltd. 2010 Promoters 1.39
20 Microns Ltd. 2010 Promoters 0.33
;
run;
proc sort data=have out=sorted;
by company_name year type descending percent_shares;
run;
data want;
set sorted;
by company_name year type descending percent_shares;
retain first_largest second_largest flg;
if first.type then do;
flg=1;
first_largest =percent_shares;
second_largest=.;
end;
else flg+1;
if flg=2 or (first.type and last.type) then do;
second_largest=percent_shares;
output;
end;
drop percent_shares flg;
run;
It would be helpful if you could present the sample data in a usable form in the DATA step if possible.
What should percent_shares contain in the want data set?
Classic use of the IDGROUP Option in Proc Summary
proc summary data=have nway;
class company_name year type;
var percent_shares;
output out=want(drop=_:) idgroup (max(percent_shares) out[2] (percent_shares) = largest);
run;
Look like the same problem you have discussed in https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdi...
Ha. I just test it . If you have two same MAX value and PROC SUMMARY would keep them all.
If you want display two unique max value .
data have;
length company_name $15 year 8 type $16 percent_shares 8;
input company_name $ 1-15 year type $ 22-37 percent_shares;
datalines;
20 Microns Ltd. 2010 Locked-In Shares 0.07
20 Microns Ltd. 2010 Locked-In Shares 0.77
20 Microns Ltd. 2010 Locked-In Shares 2.5
20 Microns Ltd. 2010 Locked-In Shares 0.57
20 Microns Ltd. 2010 Locked-In Shares 0.35
20 Microns Ltd. 2010 Locked-In Shares 1.95
20 Microns Ltd. 2010 Locked-In Shares 1.85
20 Microns Ltd. 2010 Locked-In Shares 0.1
20 Microns Ltd. 2010 Locked-In Shares 10.76 /*same value*/
20 Microns Ltd. 2010 Locked-In Shares 10.76 /*same value*/
20 Microns Ltd. 2010 Locked-In Shares 0.37
20 Microns Ltd. 2010 Locked-In Shares 0.01
20 Microns Ltd. 2010 Locked-In Shares 9.26
20 Microns Ltd. 2010 Locked-In Shares 0.61
20 Microns Ltd. 2010 Locked-In Shares 0.13
20 Microns Ltd. 2010 Locked-In Shares 0.14
20 Microns Ltd. 2010 Locked-In Shares 0.08
20 Microns Ltd. 2010 Locked-In Shares 0.12
20 Microns Ltd. 2010 Locked-In Shares 0.03
20 Microns Ltd. 2010 Locked-In Shares 1.65
20 Microns Ltd. 2010 Locked-In Shares 0.21
20 Microns Ltd. 2010 Locked-In Shares 0.03
20 Microns Ltd. 2010 Locked-In Shares 0.04
20 Microns Ltd. 2010 Locked-In Shares 0.03
20 Microns Ltd. 2010 Locked-In Shares 1.95
20 Microns Ltd. 2010 Locked-In Shares 0.1
20 Microns Ltd. 2010 Locked-In Shares 0
20 Microns Ltd. 2010 Locked-In Shares 6.81
20 Microns Ltd. 2010 Locked-In Shares 1.25
20 Microns Ltd. 2010 Locked-In Shares 0.58
20 Microns Ltd. 2010 Locked-In Shares 0.18
20 Microns Ltd. 2010 Locked-In Shares 0.18
20 Microns Ltd. 2010 Locked-In Shares 1.05
20 Microns Ltd. 2010 Locked-In Shares 0.36
20 Microns Ltd. 2010 Locked-In Shares 1.39
20 Microns Ltd. 2010 Locked-In Shares 0.07
20 Microns Ltd. 2010 Locked-In Shares 0.64
20 Microns Ltd. 2010 Locked-In Shares 0.07
20 Microns Ltd. 2010 Locked-In Shares 0.33
20 Microns Ltd. 2010 Locked-In Shares 0.17
20 Microns Ltd. 2010 Non-Promoters 18.67
20 Microns Ltd. 2010 Non-Promoters 1.65
20 Microns Ltd. 2010 Promoters 0.77
20 Microns Ltd. 2010 Promoters 2.5
20 Microns Ltd. 2010 Promoters 0.57
20 Microns Ltd. 2010 Promoters 0.35
20 Microns Ltd. 2010 Promoters 1.95
20 Microns Ltd. 2010 Promoters 1.85
20 Microns Ltd. 2010 Promoters 10.8
20 Microns Ltd. 2010 Promoters 18.94
20 Microns Ltd. 2010 Promoters 0.14
20 Microns Ltd. 2010 Promoters 0.12
20 Microns Ltd. 2010 Promoters 0.08
20 Microns Ltd. 2010 Promoters 0.12
20 Microns Ltd. 2010 Promoters 0.21
20 Microns Ltd. 2010 Promoters 1.95
20 Microns Ltd. 2010 Promoters 6.81
20 Microns Ltd. 2010 Promoters 0.06
20 Microns Ltd. 2010 Promoters 1.19
20 Microns Ltd. 2010 Promoters 0.58
20 Microns Ltd. 2010 Promoters 1.05
20 Microns Ltd. 2010 Promoters 1.39
20 Microns Ltd. 2010 Promoters 0.33
;
run;
proc sort data=have ;by company_name year type ;run;
ods select none;
ods output ExtremeValues=ExtremeValues;
proc univariate data=have nextrval=2;
by company_name year type;
var percent_shares;
run;
ods select all;
proc sort data=ExtremeValues;by company_name year type descending High;run;
proc transpose data=ExtremeValues out=want(drop=_:) prefix=largest_;
by company_name year type;
var High;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.