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

How to find the maximum number of sales in datastep?

Babloo — Wed, 16 Mar 2016 17:26:41 GMT

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.

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

Reeza — Wed, 16 Mar 2016 17:49:53 GMT

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;

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

FreelanceReinh — Wed, 16 Mar 2016 18:04:43 GMT

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;

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

Astounding — Wed, 16 Mar 2016 18:38:09 GMT

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.