SAS & Primary Indexes

Analytics

SAS & Primary Indexes

Does SAS have the capability to explicitly specify a PI as a result of a select statement the way you can in TD?

If you can do this in TD:

CREATE SET TABLE db1.table1 AS
(SELECT name, ssn, address
FROM db2.customers)
WITH DATA
UNIQUE PRIMARY INDEX (ssn);

How do you do that in SAS - or can you?
2 REPLIES
Enthusiast

Re: SAS & Primary Indexes

Basically, yes. If you are have a connection to a Teradata data source using the SAS LIBNAME mechanism, you can use the "dbcreate_table_opts" SAS data set option to specify any table create option. For example:

libname DB1 teradata user="&terauser" password="&terapass"
schema=DB1 DBCOMMIT=0;
libname DB2 some-other-data-source;
proc sql;
create table DB1.table1(dbcreate_table_opts='primary index(ssn)') as
SELECT name, ssn, address
FROM db2.customers;
quit;

I'd further recommend that you look up the "dbtype" SAS data set option if you choose to do this. That lets you explicitly define the column data types.

Of course, doing it this way only makes sense if your DB2 library is NOT in the same Teradata environment. In other words, if you're creating a Teradata table from a Teradata table, it's much better to use SAS's "pass-thru" facility, with which you can submit normal Teradata SQL:

proc sql;
connect to teradata (user="&terauser" password="&terapass" mode=teradata);
execute ( /* Your original code follows */
CREATE SET TABLE db1.table1 AS
(SELECT name, ssn, address
FROM db2.customers)
WITH DATA
UNIQUE PRIMARY INDEX (ssn)
) by teradata;
quit;

Re: SAS & Primary Indexes



I'm not clever enough to diagnose it yet, but this form isn't working for me. Err msg:
"ERROR: Invalid option name DBCREATE_TABLE_OPTS."

Fragment if my code:
----cut here----
proc sql noprint /* inobs=20 */ ;
create table &delitz
( dbcreate_table_opts = 'primary index(deposit_item_id,deposit_id)' )
as
select distinct

a.deposit_item_id
, b.deposit_id
, &idt as Date format date7.
, a.check_status
----cut here----

I will welcome ideas, suggestions. Thanks.