I have tried to find the answer for this but have had no luck. I have searched and searched my SAS books and online and found nothing that works. Here is my code: proc sql; create table pci.alla as (select distinct mbr_sys_id, substr(clm_aud_nbr, 1,10) as claim, fst_srvc_dt, adj_hccc_cd, prov_tin, mpin, full_nm, chrg_sts_cd, AMA_PL_OF_SRVC_DESC, MAJ_MKT_NM, max(mkt_row_eff_dt) as mkt_row_eff_dt format=date9., mkt_row_end_dt, finc_arng_cd, sys_drg_cd from pci.all group by mbr_sys_id, clm_aud_nbr, fst_srvc_dt, adj_hccc_cd, prov_tin, mpin, full_nm, chrg_sts_cd, AMA_PL_OF_SRVC_DESC, MAJ_MKT_NM, mkt_row_end_dt, finc_arng_cd, sys_drg_cd); run; The results are duplicated for the mkt row eff dt. It comes over like this taxid mkt row eff dt maj mkt nm 111 01jan1996 new england 111 01jan1997 maine 111 01jan1998 connecticut 222 01feb1990 new england 222 01feb1991 new jersey 222 01feb1997 new york 333 01sep1990 new york 333 01sep1993 new jersey 333 01sep2004 philadelphia 444 01dec1998 pittsburgh 444 01sep2006 new york The goal is to get the maximum of the mkt row eff dt with its corresponding market name. So in doing the max (and I am only doing these rows because the output is very large and also PHI related and just doing an example here so I realize I am missing the mbr_sys_id output, etc, etc.........the problem is solely in the mkt row eff dt that I am not getting the maximum date per the taxid) What I should see is all the fields returned but it should be like this: taxid mkt row eff dt maj mkt nm 111 01jan1998 connecticut 222 01feb1997 new york 333 01sep2004 philadelphia 444 01sep2006 new york This is because for instance taxid 111 that provider practiced in new england and maine prior to connecticut. Connecticut is the last place they practiced as of 1998 therefore I need that date but my max is not working. It gives me all of the rows and I know it has something to do with it is seeing 01jan 3 times and not the year. However, as you see......there are also variances in the months.
... View more
