BookmarkSubscribeRSS Feed
BarryParish
Calcite | Level 5
Trying to join a SAS dataset to an Oracle table. This works ALMOST all the time. But whenever we try to use a SELECT against the dataset with an IN clause, the system hangs and SAS never returns. We can use that same SAS dataset in a join and it works find. Any ideas are appreciated.

This code works:
/* build a libname to read Oracle as if it were a SAS dataset */
libname teddblib oracle user=us025580 password=Mavfh10 path='Lunar_gdwp1' schema='dm4';

proc sql;
select S.PROFIT_CENTER_ABBR_NM
, s.tyco_electronics_corp_part_nbr
,sum( S.S_CNST_SHIPPED_LOC_CRNC_AMT) format=DOLLAR12.
from teddblib.DM4133_COSTED_SALES_SMRS_V s
join saspartds sasdb /* sas dataset */
on s.tyco_electronics_corp_part_nbr=sasdb.part_number
group by s.profit_center_abbr_nm
, s.tyco_electronics_corp_part_nbr;
quit;

This code works:
proc sql;
select part_number from saspartds;
quit;


So why won't this code work??? This code freezes SAS with hourglass and never returns:
/* build a libname to read Oracle as if it were a SAS dataset */
libname teddblib oracle user=us025580 password=Mavfh10 path='Lunar_gdwp1' schema='dm4';

proc sql;
select S.PROFIT_CENTER_ABBR_NM
, s.tyco_electronics_corp_part_nbr
,sum( S.S_CNST_SHIPPED_LOC_CRNC_AMT) format=DOLLAR12.
from teddblib.DM4133_COSTED_SALES_SMRS_V s
where s.tyco_electronics_corp_part_nbr
in (select part_number from saspartds)
group by s.profit_center_abbr_nm
, s.tyco_electronics_corp_part_nbr;
quit;



In all of the above example, the SAS dataset is a simple dataset created with the code below:
/* build a temporary SAS dataset */
data work.saspartds;
length part_number $18 saspartinfo $10;
input part_number $ saspartinfo $;
cards;
6651816-1 infoon6651816-1
1740259-1 infoon1740259-1
;
run;
3 REPLIES 3
Flip
Fluorite | Level 6
Have you tried this with a very small SAS table. I'm not sure why it would hang with the ORACLE table other than it running the subquery for each observation in the SAS table and it taking a very long time.
Doc_Duke
Rhodochrosite | Level 12
I agree with Flip. The code that runs reads the Oracle table just once and transfers it to the SAS server for joining with the SAS table. The IN clause reads the Oracle table once for EACH observation in the SAS table.
Bill
Quartz | Level 8
When I want to include some SAS data into an Oracle extract, I first build a temporary Oracle table and include that as part of the extract syntax. It's probably gives better performance as well. Code below.

libname dw oracle user=userid
password=pw
path="@tns:xyx, buffsize=5000";

**Upload temptable to Oracle;
data dw.temptable;
set temptable;
run;

proc sql;
connect to oracle
(user=userid orapw=pw path="@tns:xyx, buffsize=5000");
**Analyze Oracle table for optimization purposes;
execute( analyze table temptable estimate statistics sample 1 percent) by oracle;
run;
quit;

Be sure to delete the temp table when done.

proc sql;
connect to oracle
(user=userid orapw=pw path="@tns:xyx, buffsize=5000");

execute (drop table temptable) by oracle;
run;
quit;

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