SAS SQL Create Table & Group by problem when querying a Teradata DB.

Database

SAS SQL Create Table & Group by problem when querying a Teradata DB.

I'm hoping someone can help.  The group by in this code will not work for some reason.  It's fine when it's simply a "select" statement.  The moment I try to do a 'create table' it doesn't like the group by. 

Here's the code:

PROC SQL;

CONNECT TO TERADATA as tera2

 (USER= xxxxxx

 PASSWORD= xxxxxx

 tdpid=tdprod);

create table test1 as

SELECT * from connection to tera2

(select

a.SRC_CUST_ID as custid,

a.CUST_NM  as name

FROM db.CUST a

WHERE a.SRC_CUST_ID= 'xxxxxxxxx'

GROUP BY a.SRC_CUST_ID,

        a.CUST_NM

);

disconnect from teradata;

quit;

4 REPLIES
Junior Supporter

Re: SAS SQL Create Table & Group by problem when querying a Teradata DB.

Hi.

The part "AS …WITH (NO) DATA (AND STATISTICS)" is missing in your code. Refer to the docs to look up the correct syntax for CREATE TABLE AS.

Cheers.

Carlos.

Enthusiast

Re: SAS SQL Create Table & Group by problem when querying a Teradata DB.

Hi... someone can help me with some fefault create group template?

Cannot find any for teradata :( 

Enthusiast

Re: SAS SQL Create Table & Group by problem when querying a Teradata DB.

Hi... someone can help me with some fefault create group template?

Cannot find any for teradata :( 

N/A

Re: SAS SQL Create Table & Group by problem when querying a Teradata DB.

Try this

create table test1 as

(SELECT * from connection to tera2

(select

a.SRC_CUST_ID as custid,

a.CUST_NM  as name

FROM db.CUST a

WHERE a.SRC_CUST_ID= 'xxxxxxxxx'

GROUP BY a.SRC_CUST_ID,

        a.CUST_NM

)dt)with data and stats;