issue with explicit pass through proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

issue with explicit pass through proc sql

I just cannot determine what's wrong with this code:

 

PROC SQL INOBS=10;
   CONNECT TO TERADATA AS TeraDataConn
    (BULKLOAD=YES MODE=TERADATA user="bla" password="XXXX");

   CREATE TABLE WORK.Test123 AS 
   ( 
   		SELECT
        	SomeColumn 
		FROM SomeTable;
   ); 

   DISCONNECT FROM TeraDataConn;
QUIT; 

It should return the TOP 10 values of SomeColumn in SomeTable.


Accepted Solutions
Solution
‎01-13-2017 07:17 AM
Respected Advisor
Posts: 4,135

Re: issue with explicit pass through proc sql

Besides of what others already wrote, if the part in bracket should execute within Teradata then you need to instruct SAS so. 

Currently you're just connecting and disconnecting to Teradata but you're not running any code in a pass-through block.

   CREATE TABLE WORK.Test123 AS 
   ( 
   		SELECT
        	SomeColumn 
		FROM SomeTable;
   ); 

 

From the top of my hat how this should look like:

   CREATE TABLE WORK.Test123 AS 
   select * from connection to TeraDataCom
   ( 
   		SELECT
        	SomeColumn 
		FROM SomeTable;
   ); 

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

Re: issue with explicit pass through proc sql

And what does it do?  You could try posting errors/warnings, examples of what is "not working" etc.

 

From what I see it looks ok, however there is nothing in the code to indicate that it should take only the top 10 responses, it is just selecting all data.  Also, what do you mean by top 10 results, do you only want 10 obs, or do you mean all obs with a value in the top 10?  Is this perhaps the cause:

http://support.sas.com/kb/45/216.html

 

Does terradata have an Obs type function (monotonic() in SAS, rownumber in Oracle etc.), if so then order your data by value, and take only observations with rownumber <= 10.

Super Contributor
Posts: 474

Re: issue with explicit pass through proc sql

Hi.

 

Does not work with pass through.

 

You have to limit the sample from within your query, which in Teradata I think is achieved like this:

 

select SomeColumn 
from SomeTable
sample 10;

Pass throught runs in the database, there wouldn't be much advantage of limiting the sample on the SAS side (INOBS) as the whole table would have to be pulled to SAS and afterward returned the first 10 rows.

 

Daniel Santos @ www.cgd.pt

Solution
‎01-13-2017 07:17 AM
Respected Advisor
Posts: 4,135

Re: issue with explicit pass through proc sql

Besides of what others already wrote, if the part in bracket should execute within Teradata then you need to instruct SAS so. 

Currently you're just connecting and disconnecting to Teradata but you're not running any code in a pass-through block.

   CREATE TABLE WORK.Test123 AS 
   ( 
   		SELECT
        	SomeColumn 
		FROM SomeTable;
   ); 

 

From the top of my hat how this should look like:

   CREATE TABLE WORK.Test123 AS 
   select * from connection to TeraDataCom
   ( 
   		SELECT
        	SomeColumn 
		FROM SomeTable;
   ); 
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 135 views
  • 0 likes
  • 4 in conversation