topic Re: Proc SQL: Where with IN freezes SAS in SAS Procedures

Proc SQL: Where with IN freezes SAS

BarryParish — Fri, 08 Jan 2010 18:26:52 GMT

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;

Re: Proc SQL: Where with IN freezes SAS

Flip — Fri, 08 Jan 2010 19:37:26 GMT

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.

Re: Proc SQL: Where with IN freezes SAS

Doc_Duke — Sun, 10 Jan 2010 16:54:22 GMT

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.

Re: Proc SQL: Where with IN freezes SAS

Bill — Mon, 11 Jan 2010 14:46:22 GMT

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;