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
Thanks that worked perfectly
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.
Thanks that worked perfectly
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.