topic Max amount for each customer (first. last.) in SAS Programming

Max amount for each customer (first. last.)

znhnm — Fri, 09 Dec 2022 06:59:14 GMT

Hi,

I have a table something like this:

Customer	Number	Amount
A	X	10
A	X	20
A	Y	70
B	W	40
B	Z	20
B	Z	30

I want to create this:

Customer	Number	Amount	Total Amount
A	Y	70	100
B	W	40	90

The goal is to find the highesr amount for each customer then also create a column to sum up the total for each customer. How could I do that?

I can think about the first. option but I am not sure about the syntax. I'd appreciate any support. Thanks!

Re: Max amount for each customer (first. last.)

PeterClemmensen — Fri, 09 Dec 2022 07:47:37 GMT

Try this

data have;
input Customer $ Number $ Amount;
datalines;
A X 10
A X 20
A Y 70
B W 40
B Z 20
B Z 30
;

proc sort data = have;
   by Customer Amount;
run;

data want;
   set have;
   by Customer Amount;
   if first.Customer then total = 0;
   total + Amount;
   if last.Customer;
run;

Re: Max amount for each customer (first. last.)

znhnm — Fri, 09 Dec 2022 08:39:54 GMT

Thank you so much! The first. works. I mean, I can get the highest amount for each customer. However, for the total, all I see is 0. Can you think of a reason why?

Re: Max amount for each customer (first. last.)

PeterClemmensen — Fri, 09 Dec 2022 08:41:19 GMT

Did you run my code exactly as posted?

Re: Max amount for each customer (first. last.)

ballardw — Fri, 09 Dec 2022 09:09:07 GMT

@znhnm wrote:

Hi,

I have a table something like this:

Customer

Number

Amount

A

X

10

A

X

20

A

Y

70

B

W

40

B

Z

20

B

Z

30

I want to create this:

Customer

Number

Amount

Total Amount

A

Y

70

100

B

W

40

90

The goal is to find the highesr amount for each customer then also create a column to sum up the total for each customer. How could I do that?

I can think about the first. option but I am not sure about the syntax. I'd appreciate any support. Thanks!

One way:

proc summary data=have nway;
   class customer;
   var amount;
   output out=want(drop=_:)
       max(amount)=Amount sum(amount)=totalamount
       maxid(amount(Number))=Number
   ;
run;

The procedures Means and Summary have many options for getting statistics of multiple variables by groups.

They also include, as shown, ways to get the value of an identification variable (or multiple variables) that would be the identification value of a Max or Min value (which appears to be desired but was not even mentioned in your problem description).

The Drop=_: data set option removes to two other variables _type_ and _freq_ that would indicate the combination of class or by variables and the count of records used. The Nway option results in only the combination of all Class or By variables. The procedure if you did not include the option would have an additional row of data that included the summary variables for all records as well.

Re: Max amount for each customer (first. last.)

PaigeMiller — Fri, 09 Dec 2022 11:56:46 GMT

In my opinion, PROC SUMMARY is definitely the way to go, instead of using a DATA step. All of the necessary calculations have been built into PROC SUMMARY, debugged and tested, and handle missing values properly; whereas in a data step, you would have to do the debugging and testing and handling of missing values yourself.

Re: Max amount for each customer (first. last.)

znhnm — Fri, 09 Dec 2022 15:59:03 GMT

Thank you so much! I had a typo and when I fixed it to be exactly the same with your suggestion, it run perfectly. Thanks!

Re: Max amount for each customer (first. last.)

znhnm — Fri, 09 Dec 2022 16:12:34 GMT

I had no idea about these details. Thanks.