topic Re: Largest by id and service in SAS Procedures

Largest by id and service

tmcrouse — Fri, 14 Feb 2014 21:24:27 GMT

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;

Re: Largest by id and service

Reeza — Fri, 14 Feb 2014 21:34:05 GMT

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.

proc means data=have noprint;

class id;

var netpdamt;

output out=want max(netpdamt)=largest;

ID service;

run;

Re: Largest by id and service

ballardw — Fri, 14 Feb 2014 21:37:12 GMT

This might work.

proc summary data=have nway;

class Id ;

var netpdamt;

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

run;