topic Re: how to get maxdate and claimid in SAS Programming

how to get maxdate and claimid

SASPhile — Mon, 10 May 2010 01:01:27 GMT

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

Re: how to get maxdate and claimid

ArtC — Mon, 10 May 2010 01:19:48 GMT

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]

Re: how to get maxdate and claimid

SASPhile — Mon, 10 May 2010 01:30:10 GMT

ArtC,
How to get this in proc Sql?

Claimid for the latest date and its max row number?

Re: how to get maxdate and claimid

ArtC — Mon, 10 May 2010 05:45:00 GMT

Try something like:

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

Re: how to get maxdate and claimid

data_null__ — Mon, 10 May 2010 14:30:57 GMT

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]

Re: how to get maxdate and claimid

Doc_Duke — Mon, 10 May 2010 23:57:35 GMT

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

Re: how to get maxdate and claimid

ArtC — Tue, 11 May 2010 00:33:47 GMT

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.

Re: how to get maxdate and claimid

Doc_Duke — Tue, 11 May 2010 01:20:41 GMT

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.

Re: how to get maxdate and claimid

data_null__ — Tue, 11 May 2010 11:27:29 GMT

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.