topic issue with explicit pass through proc sql in SAS Programming

issue with explicit pass through proc sql

csetzkorn — Fri, 13 Jan 2017 10:05:47 GMT

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.

Re: issue with explicit pass through proc sql

RW9 — Fri, 13 Jan 2017 10:15:15 GMT

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.

Re: issue with explicit pass through proc sql

DanielSantos — Fri, 13 Jan 2017 10:15:31 GMT

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

Re: issue with explicit pass through proc sql

Patrick — Fri, 13 Jan 2017 10:51:50 GMT

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;
   );