Hi. I've had to change some code from SQL Pass-Thru to Proc SQL and while the Where conditions haven't changed the number of records being pulled has changed to an incorrect number.
SQL Pass-Thru is pulling directly from the Oracle tables where ACTUAL_DLVRY_DATE is defined as a Date type. Whereas the Proc SQL is referencing an Oracle Libname instead. Can anyone identify what is going wrong and how it can be corrected?
Any help would be greatly appreciated!
This SQL Pass-Thru code pulls the correct number of records at 341.
proc sql;
connect to oracle as db (user=&orauser password=&orapass path="ivasprd");
create table QueryData as
select * from connection to db
( select b.imb_code
from ivprl.bi_spm_piece_iv_recon a, ivprl.bi_spm_piece_bids_recon b
where a.imb_code = b.imb_code
and A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL
);
disconnect from db;
quit;
But this Proc SQL code pulls the wrong number of records at 29
libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVPRL";
proc sql;
create table QueryData%sysfunc(tranwrd(&rule_order,.,_)) as
select b.imb_code length = 31
from iv_ora.bi_spm_piece_iv_recon a, iv_ora.bi_spm_piece_bids_recon b
where a.imb_code = b.imb_code
and A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL;
quit;
It probably has to do with the use of NULL.
Try
options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
which will return how SAS/ACCESS libname will translate your SAS SQL query into Oracle SQL.
It probably has to do with the use of NULL.
Try
options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
which will return how SAS/ACCESS libname will translate your SAS SQL query into Oracle SQL.
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.
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.