max not working

[tmm]

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

[Reeza]

Because you've grouped by date as well

group by member_id, fst_srvc_dt, prov_tin, proc_cd, adj_hccc_cd, prov_row_eff_dt, prov_nm);

So it takes each level of the date as a new group like you've requested.

If its in a summary function such as max(), Mean(), n() etc. you do not need it in the group by clause.

[tmm · Accepted solution]

Thanks that worked perfectly