BookmarkSubscribeRSS Feed
TajisDawn
Calcite | Level 5

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;

10 REPLIES 10
GreggB
Pyrite | Level 9

What error message are you getting?

TajisDawn
Calcite | Level 5

@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.

SASKiwi
PROC Star

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

GreggB
Pyrite | Level 9

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;

TajisDawn
Calcite | Level 5

@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.

SASKiwi
PROC Star

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 

 

 

SASKiwi
PROC Star

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.

TajisDawn
Calcite | Level 5

@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).

SuryaKiran
Meteorite | Level 14

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;

Thanks,
Suryakiran
TajisDawn
Calcite | Level 5


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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1468 views
  • 0 likes
  • 4 in conversation