DATA Step, Macro, Functions and more

Picking the largest two observations in sales within the same industry

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Picking the largest two observations in sales within the same industry

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.

 

 

obscyearsic2epidsumauditorsalesumsicsale
12016134115661350217586.14
22016142009774011.517586.14
320161420190845.57717586.14
420161206000010.4117586.14
5201616560900126.65317586.14
6201612.77E+08017586.14
72016742018021691.3565106.032
820167185000332516.8635106.032
92016719900031845.6785106.032
10201677290001552.1355106.032
11201675.77E+0805106.032

 

Thank you


Accepted Solutions
Solution
‎01-28-2018 10:52 PM
PROC Star
Posts: 1,299

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

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

 

View solution in original post


All Replies
Super User
Posts: 22,826

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

See the approach outlined here:

http://documentation.sas.com/?docsetId=proc&docsetTarget=n0afy7c5m16m9dn1s60z9krjmwxq.htm&docsetVers...

 


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




Solution
‎01-28-2018 10:52 PM
PROC Star
Posts: 1,299

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

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

 

Contributor
Posts: 29

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

Posted in reply to novinosrin

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;

Contributor
Posts: 20

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

Thank you so much!

Super User
Posts: 22,826

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 127 views
  • 2 likes
  • 4 in conversation