max not working

tmm — Fri, 09 Mar 2012 20:34:11 GMT

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

Re: max not working

Reeza — Fri, 09 Mar 2012 20:44:59 GMT

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.

max not working

tmm — Fri, 09 Mar 2012 20:57:34 GMT

Thanks that worked perfectly

topic Re: max not working in SAS Procedures

max not working

Re: max not working

max not working