topic Re: Picking the largest two observations in sales within the same industry in SAS Programming

Picking the largest two observations in sales within the same industry

joon1 — Mon, 29 Jan 2018 00:37:41 GMT

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

Re: Picking the largest two observations in sales within the same industry

Reeza — Mon, 29 Jan 2018 01:03:25 GMT

See the approach outlined here:

http://documentation.sas.com/?docsetId=proc&docsetTarget=n0afy7c5m16m9dn1s60z9krjmwxq.htm&docsetVersion=9.4&locale=en#n0afy7c5m16m9dn1s60z9krjmwxq

@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

Re: Picking the largest two observations in sales within the same industry

novinosrin — Mon, 29 Jan 2018 01:51:41 GMT

@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;

Re: Picking the largest two observations in sales within the same industry

MINX — Mon, 29 Jan 2018 03:31:28 GMT

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;

Re: Picking the largest two observations in sales within the same industry

joon1 — Mon, 29 Jan 2018 04:12:54 GMT

Thank you so much!

Re: Picking the largest two observations in sales within the same industry

Reeza — Mon, 29 Jan 2018 05:06:20 GMT

What about ties? One of those answers accounts for ties, the other does not.