topic Re: Complex code conversion - Oracle SQL to SAS in SAS Programming

Complex code conversion - Oracle SQL to SAS

TajisDawn — Wed, 02 Nov 2016 17:49:58 GMT

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;

Re: Complex code conversion - Oracle SQL to SAS

GreggB — Wed, 02 Nov 2016 18:40:48 GMT

What error message are you getting?

Re: Complex code conversion - Oracle SQL to SAS

TajisDawn — Wed, 02 Nov 2016 18:49:35 GMT

@GreggB wrote:
What error message are you getting?

Hi! I'm getting 2 error messages: "syntax error", and "symbol not recognized". Both are related to the (+) being underlined with red. Then PROC SQL sets to NOEXEC. If I comment out those lines, the program runs fine but I don't get all the data I need.

Re: Complex code conversion - Oracle SQL to SAS

SASKiwi — Wed, 02 Nov 2016 18:52:08 GMT

My guess is this is a custom user-written stored function or procedure in Oracle: sf_getphonebytype. Talk to your Oracle DBA. He may know who wrote it or what it does or possibly be able to provide the source code.

Re: Complex code conversion - Oracle SQL to SAS

SASKiwi — Wed, 02 Nov 2016 18:55:08 GMT

(+) only works in Oracle - it's not standard SQL. Try LEFT JOIN instead.

Re: Complex code conversion - Oracle SQL to SAS

GreggB — Wed, 02 Nov 2016 18:58:09 GMT

I''m not that familar with Oracle. In SAS a left outer join looks like this:

PROC SQL;

SELECT *

FROM FIRST LEFT JOIN SECOND

ON FIRST.X = SECOND.X;

QUIT;

Re: Complex code conversion - Oracle SQL to SAS

TajisDawn — Wed, 02 Nov 2016 19:03:59 GMT

@SASKiwi wrote:
My guess is this is a custom user-written stored function or procedure in Oracle: sf_getphonebytype. Talk to your Oracle DBA. He may know who wrote it or what it does or possibly be able to provide the source code.

Thanks! I hadn't thought about it being a user-written function. It's very likely. I was going to just use substr, actually, because the columns for fax vs primary telephone are in the table, he's just pulling them to format with dashes (which I don't really want).

Re: Complex code conversion - Oracle SQL to SAS

TajisDawn — Wed, 02 Nov 2016 19:07:10 GMT

@GreggB wrote:
I''m not that familar with Oracle. In SAS a left outer join looks like this:

PROC SQL;
SELECT *
FROM FIRST LEFT JOIN SECOND
ON FIRST.X = SECOND.X;
QUIT;

I know how to use left joins normally, but since these are deep in a "where" statement and involves creating a lot of substatements (with one table alias the result of multiple joins), I'm a bit more baffled. I'm not very good yet at nested substatements.

Re: Complex code conversion - Oracle SQL to SAS

SASKiwi — Wed, 02 Nov 2016 19:28:48 GMT

I suggest you split out the PAR1 sub-query and get this working first - it is this sub-query that needs the LEFT JOIN:

left join

(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

) as par1

on pl.prac_id = par1.prac_id
and pl.prov_id = par1.prov_id
and pl.loc_id = par1.loc_id

Re: Complex code conversion - Oracle SQL to SAS

SuryaKiran — Wed, 02 Nov 2016 20:50:27 GMT

If you already have the oracle SQL code to run you can use the same code to run from sas using explicit SQL pass-through.

proc sql;

connect to oracle (user=myusr1 password=mypwd1);

select *

from connection to oracle

( your Oracle code here);

disconnect from oracle;

quit;

Re: Complex code conversion - Oracle SQL to SAS

TajisDawn — Mon, 07 Nov 2016 12:17:07 GMT

Thanks! I do have access to the Oracle database, so I'll give that a try.Since it will take some tweaking, I'll have to let you know after my deadlines if it worked.