DATA Step, Macro, Functions and more

How to find the maximum number of sales in datastep?

Reply
Super Contributor
Posts: 436

How to find the maximum number of sales in datastep?

I wish to know the way to find out the maximum number of sales amount by each salesperson via datastep.

 

I've a data like this

 

Salesid     Sales_amount        Salesperson

001           100                         Dean

001           200                         Dean

002           300                         Tom

002           700                         Tom

003           500                         Jacob

003           400                         Jacob

 

Desired output should be like this

 

Salesid     Sales_amount        Salesperson

001           200                         Dean

002           700                         Tom

003           500                         Jacob

 

Thanks for any help you offer me.

 

Super User
Posts: 19,815

Re: How to find the maximum number of sales in datastep?

Sort descending and then take the first record for the table.

 

proc sort data=have;
by salesid descending sales_amount;
run;

data want;
set have;
by salesid;
if first.salesid;
run;
Trusted Advisor
Posts: 1,117

Re: How to find the maximum number of sales in datastep?

[ Edited ]

Hi @Babloo,

 

Alternatively, you could use a double DOW loop:

data have;
input Salesid $ Sales_amount Salesperson $;
cards;
001 100 Dean
001 200 Dean
002 300 Tom
002 700 Tom
003 500 Jacob
003 400 Jacob
;

data want;
do until(last.salesid);
  set have;
  by salesid;
  maxamt=max(maxamt,sales_amount);
end;
do until(last.salesid);
  set have;
  by salesid;
end;
drop sales_amount;
rename maxamt=Sales_amount;
run;

proc print data=want noobs;
var salesid sales_amount salesperson;
run;
Super User
Posts: 5,509

Re: How to find the maximum number of sales in datastep?

Just curious ... why are you insisting on a DATA step solution?  Both PROC SQL and PROC SUMMARY can easily handle this without the need to sort your data.

Ask a Question
Discussion stats
  • 3 replies
  • 245 views
  • 3 likes
  • 4 in conversation