Sure. It is a long query. The items that are not commented out have been tested by my Galaxy team who enhanced the query to make it run faster. Using the entire year from what you see in the startdt and enddt takes 20 minutes to run. When I add the cert_of_cov_fl_yr and then do any of 7 possible joins about 45mins to an hour and 15mins later it will say disk system full. So I changed my query to run just for 1 day. Using any of the 7 possible joins to the table that houses cert_of_cov_fl_yr, it runs about 3 hours on any of them and returns 695 rows. There are no other possible joins listed in our data dictionary. So I am sure since it takes 3 hours to return 695 rows that account for 1 day, trying to pull the whole year it just cannot handle because 365 days worth of data multiply by 3 hours would be a total runtime of 1080 hours. So not sure what the deal is with that one particular table. And the sad thing is I cannot take that field out because the PM needs it. I can also not just remove it for now and run the one that does run for 20minutes then do a group by the clm_aud_nbr and mbr_sys_id and do a left join to the contract plan coverage table because I am connecting via a DB2 and we cannot take data we extract onto our local machine and link it back to the server in SAS. I can do it in Access and have attempted but it is too large for Access to handle and Access will bomb. options compress=yes reuse=yes; %let pass='Year2012'; %let startdt='06/01/2011'; %let enddt='05/31/2011'; %let hgh=('80428','80429','80430','83003','84305','86277','J2940','J2941','S9558','Q0515'); proc sql; connect to db2(database=dsn user=username password=&pass); create table hgh.claims as select * from connection to db2 (SELECT a.clm_aud_nbr, mbr_sys_id, fst_srvc_dt, sbscr_nbr, policy_nbr, mbr_lst_nm, mbr_fst_nm, adj_hccc_cd, prov_tin, proc_cd, diag_1_cd, diag_2_cd, diag_3_cd, diag_4_cd, mpin, full_nm, prdct_cd, company_cd, grp_cust_nbr, er_grp_id, ben_pln_nbr, cert_of_cov_fl_yr, er_grp_id, grp_cust_nbr, ben_pln_nbr, ben_strct_1_cd, ben_strct_2_cd, ben_cov_typ_cd, mbr_cov_mo_row_eff_dt, mbr_cov_mo_row_end_dt, finc_arng_cd, finc_arng_desc, ama_pl_of_srvc_cd, ama_pl_of_srvc_desc, orig_srvc_cd, member_mkt, provider_mkt, bil_typ_cd, alloc_rvnu_cd, fm_typ_cd, fm_typ_desc, sum(allw_amt) as alloc_allw_amt, sum(elig_amt) as alloc_elig_amt, sum(net_pd_amt) as alloc_net_pd_amt, sum(sbmt_chrg_amt) as alloc_sbmt_chrg_amt FROM ( SELECT DISTINCT unet.clm_aud_nbr FROM galaxy.unet_claim_statistical_service unet INNER JOIN galaxy.unet_claim_header head ON unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id AND unet.mbr_sys_id = head.mbr_sys_id INNER JOIN galaxy.company_code co ON head.co_cd = co.co_cd INNER JOIN galaxy.product prod ON head.prdct_cd = prod.prdct_cd INNER JOIN galaxy.business_line_code blc ON prod.bus_line_cd = blc.bus_line_cd INNER JOIN galaxy.member_coverage_month mem ON unet.mbr_sys_id = mem.mbr_sys_id AND unet.fst_srvc_dt BETWEEN mem.mbr_cov_mo_row_eff_dt AND mem.mbr_cov_mo_row_end_dt WHERE co.finc_arng_cd IN ('F', 'A') AND mem.cob_ind = ('N') AND blc.bus_line_desc = 'COMMERCIAL' AND unet.fst_srvc_dt BETWEEN &startdt and &enddt AND unet.chrg_sts_cd IN ('P') AND unet.proc_cd IN &hgh and unet.allw_amt >0 order by 1 ) b INNER JOIN (SELECT unet.clm_aud_nbr, unet.mbr_sys_id, unet.sbscr_nbr, head.co_cd as company_cd, head.mbr_lst_nm, head.mbr_fst_nm, mem.grp_cust_nbr, head.ben_strct_1_cd, head.ben_strct_2_cd, head.ben_cov_typ_cd, unet.cust_seg_nbr AS policy_nbr, unet.fst_srvc_dt, unet.adj_hccc_cd, unet.prov_tin, unet.proc_cd, unet.diag_1_cd, unet.diag_2_cd, unet.diag_3_cd, unet.diag_4_cd, prov.mpin, prov.full_nm, co.finc_arng_cd, co.finc_arng_desc, head.prdct_cd, plc.ama_pl_of_srvc_cd, plc.ama_pl_of_srvc_desc, market.maj_mkt_nm AS member_mkt, market2.maj_mkt_nm AS provider_mkt, unet.orig_srvc_cd, bill.bil_typ_cd, mem.mbr_cov_mo_row_eff_dt, mem.mbr_cov_mo_row_end_dt, unet.rvnu_cd as alloc_rvnu_cd, mem2.er_grp_id, mem3.ben_pln_nbr, cert.cert_of_cov_fl_yr, head.fm_typ_cd, frm.fm_typ_desc, allw_amt, elig_amt, net_pd_amt, sbmt_chrg_amt FROM galaxy.unet_claim_statistical_service unet INNER JOIN galaxy.unet_claim_header head ON unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id AND unet.mbr_sys_id = head.mbr_sys_id INNER JOIN galaxy.provider prov ON unet.srvc_prov_row_eff_dt = prov.prov_row_eff_dt AND unet.srvc_prov_sys_id = prov.prov_sys_id INNER JOIN galaxy.company_code co ON head.co_cd = co.co_cd inner join galaxy.product prod ON head.prdct_cd = prod.prdct_cd INNER JOIN galaxy.place_of_service_code plc ON unet.pl_of_srvc_sys_id = plc.pl_of_srvc_sys_id INNER JOIN galaxy.unet_market market ON head.geo_area_nbr = market.mkt_nbr AND head.geo_area_nbr_mkt_row_eff_dt = market.mkt_row_eff_dt inner join galaxy.UNET_FACILITY_UB92 ub92 on head.mbr_sys_id=ub92.mbr_sys_id and head.UNET_CLM_HEAD_SYS_ID =ub92.UNET_CLM_HEAD_SYS_ID INNER JOIN galaxy.cosmos_bill_type_code bill ON ub92.bil_typ_cd = bill.bil_typ_cd INNER JOIN galaxy.unet_market market2 ON unet.prov_contr_mkt_nbr = market2.mkt_nbr AND unet.prov_contr_mkt_row_eff_dt = market2.mkt_row_eff_dt inner join galaxy.member_coverage_month mem on unet.mbr_sys_id = mem.mbr_sys_id and unet.fst_srvc_dt between mem.mbr_cov_mo_row_eff_dt and mem.mbr_cov_mo_row_end_dt inner join galaxy.member mem2 on head.mbr_sys_id = mem2.mbr_sys_id inner join galaxy.member_coverage mem3 on mem2.mbr_sys_id = mem3.mbr_sys_id /*inner join galaxy.customer_segment cust on unet.cust_seg_sys_id = cust.cust_seg_sys_id and unet.cust_seg_row_eff_dt = cust.cust_seg_row_eff_dt inner join galaxy.parent_customer parent on cust.par_cust_nbr = parent.par_cust_nbr and cust.src_sys_cd = parent.src_sys_cd INNER JOIN galaxy.contract_plan_coverage cert on parent.par_cust_sys_id = cert.par_cust_sys_id*/ /*first poss if using cust to parent*/ inner join galaxy.contract_plan_coverage cert on prod.prdct_cd = cert.prdct_cd and prod.src_sys_cd = cert.src_sys_cd inner join galaxy.form_type_code frm on head.fm_typ_cd = frm.fm_typ_cd ) a ON a.clm_aud_nbr = b.clm_aud_nbr group by a.clm_aud_nbr, mbr_sys_id, sbscr_nbr, policy_nbr, mbr_lst_nm, mbr_fst_nm, fst_srvc_dt, adj_hccc_cd, prov_tin, proc_cd, diag_1_cd, diag_2_cd, diag_3_cd, diag_4_cd, mpin, full_nm, prdct_cd, company_cd, ben_strct_1_cd, ben_strct_2_cd, ben_cov_typ_cd, mbr_cov_mo_row_eff_dt, mbr_cov_mo_row_end_dt, finc_arng_cd, finc_arng_desc, ama_pl_of_srvc_cd, ama_pl_of_srvc_desc, orig_srvc_cd, member_mkt, provider_mkt, bil_typ_cd, alloc_rvnu_cd, er_grp_id, grp_cust_nbr, ben_pln_nbr, cert_of_cov_fl_yr, fm_typ_cd, fm_typ_desc) order by clm_aud_nbr; disconnect from db2; quit;
... View more