DATA Step, Macro, Functions and more

how to get maxdate and claimid

Reply
Super Contributor
Posts: 647

how to get maxdate and claimid

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
Valued Guide
Posts: 632

Re: how to get maxdate and claimid

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]
Super Contributor
Posts: 647

Re: how to get maxdate and claimid

ArtC,
How to get this in proc Sql?

Claimid for the latest date and its max row number?
Valued Guide
Posts: 632

Re: how to get maxdate and claimid

Try something like:

[pre]
proc sql;
select claimid, date, rownumber
from claims
group by claimid
having date=max(date)
;
quit;
[/pre]
Respected Advisor
Posts: 3,777

Re: how to get maxdate and claimid

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

[pre]
proc summary data=claims nway;
class claimid;
output out=claim2(drop=_Smiley Happy idgroup(max(date) out(date rownumber)=);
run;
[/pre]
Trusted Advisor
Posts: 2,113

Re: how to get maxdate and claimid

SUMMARY requires sorting the data. The most efficient approach my be data volume dependent.
Valued Guide
Posts: 632

Re: how to get maxdate and claimid

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.
Trusted Advisor
Posts: 2,113

Re: how to get maxdate and claimid

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.
Respected Advisor
Posts: 3,777

Re: how to get maxdate and claimid

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.
Ask a Question
Discussion stats
  • 8 replies
  • 203 views
  • 0 likes
  • 4 in conversation