Help using Base SAS procedures

Proc SQL: Where with IN freezes SAS

Reply
New Contributor
Posts: 2

Proc SQL: Where with IN freezes SAS

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;
Super Contributor
Posts: 359

Re: Proc SQL: Where with IN freezes SAS

Posted in reply to BarryParish
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.
Trusted Advisor
Posts: 2,115

Re: Proc SQL: Where with IN freezes SAS

Posted in reply to BarryParish
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.
Super Contributor
Posts: 291

Re: Proc SQL: Where with IN freezes SAS

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;
Ask a Question
Discussion stats
  • 3 replies
  • 179 views
  • 0 likes
  • 4 in conversation