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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.