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;
... View more
