BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csetzkorn
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DanielSantos
Barite | Level 11

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

Patrick
Opal | Level 21

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

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
  • 3 replies
  • 1031 views
  • 0 likes
  • 4 in conversation