Hi all. I have a long code in Oracle SQL that was given to me. Unfortunately, there are a few things I haven't been able to figure out to get the code to run in SAS. (Some I have, and a few things I haven't worked on yet, like the phone and fax number part) I understand that (+) is a left outer join in the code; I just can't figure out how to convert what the code is looking for into SAS. (Using EG 6.1 and Base SAS 9.4 32 bit) Any help would be appreciated. I've tried a few things but haven't been successful in getting it worked out. PROC SQL; CREATE TABLE DOCTORS AS select distinct p.id AS PRAC_ID ,p.fname ,p.lname ,p.mname ,p.salutation AS SUFFIX ,p.degree ,p.dob AS DOB ,p.email ,p.sex AS GENDER ,p.ssn ,case when par.id = . then 'Non Par' else 'Par' end AS COMMERCIAL_PAR_STATUS ,case when par1.prac_id = . then 'Non Par' else 'Par' end AS HNJH_PAR_STATUS ,npi.number_uda AS PRACTITIONER_NPI ,pps.primary AS PRIMARY_SPEC_INDICATOR ,ps.ds AS PRACTITIONER_SPEC ,pp.name AS GROUP_NAME ,tin.tin AS GROUP_TAX_ID /*, replace (tin.tin, '-') "GROUP TAX-ID NO DASHES" */ ,pp.sub_prov_code AS GROUP_SUFFIX ,ptype.type_ds AS GROUP_TYPE ,ps1.ds AS GROUP_SPEC ,pl.primary AS PRIMARY_LOCATION ,v.addr1 AS SL_ADDRESS_LINE_1 ,v.addr2 AS SL_ADDRESS_LINE_2 ,v.city_name AS SL_CITY_NAME ,v.county_name AS SL_COUNTY_NAME ,v.state_id AS SL_STATE_ID ,v.zip AS SL_ZIP_CODE /*, substr(sf_getphonebytype(ptl.address_id,'FAX','-'),1,14) AS SL_FAX_NUMBER */ /*, substr(sf_getphonebytype(ptl.address_id,'DIR_PHONE','-'),1,14) AS SL_PHONE_NUMBER */ from PNO.pp_prac p ,PNO.pp_prov pp ,PNO.pp_prov_tin_prov_cycle ptpc ,PNO.pp_prov_tin tin ,PNO.pp_prov_specialty provspec ,PNO.pp_prov_type ptype ,PNO.pp_prac_loc pl ,PNO.pp_prov_loc ppl ,PNO.pp_prov_tin_loc ptl ,PNO.v_fmg_addresses v ,PNO.v_fmg_addresses v1 ,PNO.pp_prac_specialty pps ,PNO.pp_spec ps ,PNO.pp_spec ps1 ,(select prac_id, number_uda from PNO.v_uda_prac_npi where term_uda GE today() union select prac_id, number_uda from PNO.v_uda_prac_npi where term_uda is null ) npi ,(select prov_id, number_uda from PNO.v_uda_prov_npi where term_uda GE today () union select prov_id, number_uda from PNO.v_uda_prov_npi where term_uda is null) GROUPnpi ,(select p.id, pnc.start_date, pnc.end_date from PNO.pp_prac_net_cycle pnc, PNO.pp_prac p, PNO.pp_net pn where p.id = pnc.prac_id and pnc.net_id = pn.id and pn.ds in ('insured','notinsured') and pnc.end_date GE today()) par ,(select pnlc.loc_id, pnlc.prac_id, pnlc.prov_id, pncs.specialty_id from PNO.pp_prac_net_cycle pnc, PNO.pp_prac p, PNO.pp_net pn, PNO.pp_prac_net_loc_cycle pnlc, PNO.pp_prac_net_cycle_spec pncs where p.id = pnc.prac_id and pnc.net_id = pn.id and pn.ds in ('notinsured') and pnc.end_date GE today() and pnc.prac_id = pnlc.prac_id and pnc.id = pnlc.prac_net_cycle_id and pnlc.end_date GE today() and pncs.prac_net_cycle_id = pnc.id) par1 where p.id = pl.prac_id and pl.start_date LT today() and pl.end_date GE today() and pl.loc_id = ppl.loc_id and pl.prov_id = ppl.prov_id and ppl.prov_id = pp.id and tin.id = ptpc.tin_id and pp.id = ptpc.prov_id and ptpc.end_date GE today() and pp.id = provspec.prov_id and provspec.primary = 'Y' and pp.prov_type_id = ptype.id and ppl.loc_id = ptl.id and ptl.address_id = v.address_id and p.id = pps.prac_id and pps.specialty_id = ps.id and pps.primary like 'Y' and provspec.specialty_id = ps1.id and pp.address_id = v1.address_id and pl.prac_id = par1.prac_id (+) and pl.prov_id = par1.prov_id(+) and pl.loc_id = par1.loc_id (+) and p.id = par.id (+) and p.id = npi.prac_id (+) and pp.id = groupnpi.prov_id (+); QUIT;
... View more