I actually just thought of a way to do it, but I don't particularly like it. I created an ID using the function "cats" using the first 3 variables. The I created a new table which did not have the amount column and had the max from column 3 (report year). Then I recreated the ID and joined back to the original dataset below. It actually does exactly what I want, but I feel like there should be a more elegant way. Any input would be greatly appreciated.
Here's the program *generated* by EG with my point and click approach (includes your sample data).
length claim $ 1 LossYr 3 ReptYr 3 Amount 8;
input claim LossYr ReptYr Amount;
A 6 6 10000
A 6 7 20000
B 7 7 10000
B 7 8 5000
C 7 7 10000
C 7 8 10000
E 8 8 20000
F 7 7 5000
F 7 8 5000
G 7 7 5000
H 6 6 10000
H 6 7 5000
/* generated by Rank Data task */
PROC RANK DATA = WORK.SORTTempTableSorted
RANKS rank_ReptYr ;
/* generated by Query Builder */
CREATE TABLE WORK.RecentClaims AS
FROM WORK.RANKEDCLAIMS AS t1
WHERE t1.rank_ReptYr = 1;
I'm in the process of reading "The Little SAS Book" and I just stumbled across another way that I think I could do what I want. It talks about an automatic variable called FIRST.variable and LAST.variable. I feel like I could use LAST.Claim# and as long as I sort by claim number and report year, that would work. However, I'm not sure if this functions exists in SAS EG or not and if so, how do I use it.