BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

Thanks that worked perfectly

View solution in original post

2 REPLIES 2
Reeza
Super User

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
Fluorite | Level 6 tmm
Fluorite | Level 6

Thanks that worked perfectly

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1118 views
  • 3 likes
  • 2 in conversation