Largest by id and service

[tmcrouse]

I am not sure I am doing this correctly, but I have a very large dataset, 10 MILLION ROWS that I am trying to obtain the largest amount in the group. Here is an example of my data have and data need and the code, which I get an error:

DATA HAVE
ID	SERVICE	NETPDAMT
111	9000	$10.00
111	9001	$5.00
111	1010	$15.00
111	1049	$50.00
222	4000	$2.00
222	5000	$5.00
222	7000	$15.00
222	8001	$10.00
DATA NEED
ID	SERVICE	NETPDAMT
111	1049	$50.00
222	7000	$15.00

DATA NEED;

DATA HAVE;

large1=largest(1,of netpdamt);

by id service;

run;

[Reeza · Accepted solution]

Functions in SAS work across rows, not down a column, so you'll need to use a Proc, such as means/summary or proc sql.

ie

proc means data=have noprint;

class id;

var netpdamt;

output out=want max(netpdamt)=largest;

ID service;

run;

[ballardw]

This might work.

proc summary data=have nway;

     class Id ;

     var netpdamt;

     output out=want (drop=_type_ _freq_) max= maxid(netpdamt(service))=ServiceMaxAmount;

run;