how to get maxdate and claimid

[SASPhile]

claimid date rownumber
111 1/31/2010 1
111 2/28/2010 2
111 3/31/2010 3

112 1/31/2010 2
112 3/31/2010 1




how to get values like follows:
111 3/31/2010 3
112 3/31/2010 1


the req is to get the claimid for those dates which are latest and its rownumber

[ArtC]

Assuming that your table is not too very large, one way:

[pre]
data claims;
input claimid date mmddyy10. rownumber;
format date date9.;
datalines;
111 01/31/2010 1
111 02/28/2010 2
111 03/31/2010 3
112 1/31/2010 2
112 3/31/2010 1
;
run;
proc sort data=claims;
by claimid descending date;
run;

data latestclaims;
set claims;
by claimid;
if first.claimid;
run;
proc print data=latestclaims;
run;
[/pre]

[SASPhile]

ArtC,
How to get this in proc Sql?

Claimid for the latest date and its max row number?

[ArtC]

Try something like:

[pre]
proc sql;
select claimid, date, rownumber
from claims
group by claimid
having date=max(date)
;
quit;
[/pre]

[data_null__]

You may want to consider PROC SUMMARY's IDGROUP OUTPUT statement option.

[pre]
proc summary data=claims nway;
class claimid;
output out=claim2(drop=_:) idgroup(max(date) out(date rownumber)=);
run;
[/pre]

[Doc_Duke]

SUMMARY requires sorting the data. The most efficient approach my be data volume dependent.

[ArtC]

I really like Data_NULL_'s SUMMARY solution. It has the added advantage of allowing the top n values through the IDGROUP. Since the CLASS statement is being used the result is sorted on CLAIMID, but the incoming data need not be sorted.

[Doc_Duke]

I missed the CLASS statement. SASPhile will just have to experiment. He often has large datasets, so a CLASS could either run out of memory and abort or use virtual memory and take a long time.

[data_null__]

The CLASS statement may have trouble when the data have very high cardinality. I don’t know just how big that number is.

I’ve been counting AES lately by SOC HLGT and PT with several thousand AES with no problem, granted that ain’t millions. When running on a multi processor system using CLASS instead of BY, MEANS/SUMMARY can produce astounding performance.

I would not eschew CLASS just because I “think” it might not work, I would find out first.