BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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.

Data never sleeps

View solution in original post

1 REPLY 1
LinusH
Tourmaline | Level 20

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.

Data never sleeps

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!

How to Concatenate Values

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.

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
  • 1 reply
  • 634 views
  • 1 like
  • 2 in conversation