I am connecting to a DB2 server using an ODBC pass through and it will work with the max of the date as in run but it still returns all prov_row_eff_dts my code: proc sql; connect to odbc(dsn); create table claims as select * from connection to odbc (select distinct member_id, fst_srvc_dt, prov_tin, proc_cd, adj_hccc_cd, (max(prov_row_eff_dt)) as prov_eff_dt, prov_nm from co.table where member_id >0 and prov_tin >0 and prov_nm is not null and proc_cd = '39020' group by member_id, fst_srvc_dt, prov_tin, proc_cd, adj_hccc_cd, prov_row_eff_dt, prov_nm); order by member_id, fst_srvc_dt; quit; As I said it runs but the table it creates has everything listed but member_id's are duplicated and the only difference is the prov_row_eff_dt will say for a TIN 21dec99 then the same TIN will say 01jun11. Again all the items are duplicates. everything in the rows are the same except the prov_row_eff_dt example: member_id fst_srvc_dt prov_tin proc_cd adj_hccc_cd prov_eff_dt prov_nm 1111 21dec10 11110 39020 06 21dec99 john doe 1111 21dec10 11110 39020 06 01jun11 john doe So it is not selecting the max date and returning just the 1 row that is 01jun11 for eff_dt
... View more