Hi,
I would like to pick the largest two auditors in sales within the same two-digit sic industry. For instance, I would like to pick observations 1 and 2 out of industry where the second-digit sic code is 1 and observations 7 and 8 out of the second-digit sic code is 7. Which SAS program is proper? Your help will be greatly appreciated.
obs | cyear | sic2 | epid | sumauditorsale | sumsicsale |
1 | 2016 | 1 | 3411566 | 13502 | 17586.14 |
2 | 2016 | 1 | 4200977 | 4011.5 | 17586.14 |
3 | 2016 | 1 | 4201908 | 45.577 | 17586.14 |
4 | 2016 | 1 | 20600001 | 0.41 | 17586.14 |
5 | 2016 | 1 | 65609001 | 26.653 | 17586.14 |
6 | 2016 | 1 | 2.77E+08 | 0 | 17586.14 |
7 | 2016 | 7 | 4201802 | 1691.356 | 5106.032 |
8 | 2016 | 7 | 18500033 | 2516.863 | 5106.032 |
9 | 2016 | 7 | 19900031 | 845.678 | 5106.032 |
10 | 2016 | 7 | 72900015 | 52.135 | 5106.032 |
11 | 2016 | 7 | 5.77E+08 | 0 | 5106.032 |
Thank you
@joon1 do you mean something like this?
data want;
set have;
by sic;
if first.sic then n=0;
n+1;
if n in (1,2);
run;
See the approach outlined here:
@joon1 wrote:
Hi,
I would like to pick the largest two auditors in sales within the same two-digit sic industry. For instance, I would like to pick observations 1 and 2 out of industry where the second-digit sic code is 1 and observations 7 and 8 out of the second-digit sic code is 7. Which SAS program is proper? Your help will be greatly appreciated.
obs cyear sic2 epid sumauditorsale sumsicsale 1 2016 1 3411566 13502 17586.14 2 2016 1 4200977 4011.5 17586.14 3 2016 1 4201908 45.577 17586.14 4 2016 1 20600001 0.41 17586.14 5 2016 1 65609001 26.653 17586.14 6 2016 1 2.77E+08 0 17586.14 7 2016 7 4201802 1691.356 5106.032 8 2016 7 18500033 2516.863 5106.032 9 2016 7 19900031 845.678 5106.032 10 2016 7 72900015 52.135 5106.032 11 2016 7 5.77E+08 0 5106.032
Thank you
@joon1 do you mean something like this?
data want;
set have;
by sic;
if first.sic then n=0;
n+1;
if n in (1,2);
run;
You can try Proc Rank as:
proc rank data=have descending ties=low /*ties=dense*/
out=want(where=(order<=2));
by sic2 notsorted;
var sumauditorsale; ranks order;
run;
Thank you so much!
What about ties? One of those answers accounts for ties, the other does not.
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 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.