We’re smarter together. Learn from this collection of community knowledge and add your expertise.

change default when writing from SAS into Teradata

by Valued Guide on ‎08-07-2013 04:44 AM - edited on ‎10-05-2015 03:52 PM by Community Manager (431 Views)

the default as given in the documentation is that SAS $n variables become Teradata datatype CHAR(n).

Is there any way to change the default to VARCHAR(n)

???

The most relevant usage example that I could find in the doc, is at

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/p1dqaq7ub1cm1pn1ow3x9qkmkxe8....

 

Because I do this fairly often and sometimes with many wide-ish columns, I would like a generalized approach (hence seeking to temporarily override the default)

 

It becomes tedious to package every time

1 locate the name and length of every $ column in the sas data to be uploaded to teradata (that is wider than say $8)

2 generate the required syntax for the DBTYPE= option into : macroVar (sql style solution)

3 implement results among the dataset options alongside FASTLOAD=YES

It looks even more work to package as a macro or macro function (and I really wouldn't recommend a macro function approach as it is the equivalent of a data step reading all of dictionary columns)

 

Overriding the default looks so straightforward and such a minor step for SAS/Access, that I expect it is already there and I just can't find it.

 

appreciate your interest and help

 

Peter

 

This document was generated from the following discussion:

Comments
by SAS Employee JBailey
on ‎08-29-2013 11:33 AM

Hi Peter,

This is an interesting idea. I have made a note of it. It will be considered for a SAS/ACCESS Interface to Teradata new feature.

For the time being... Typically, users create the Teradata tables using DDL statements prior to loading them. I think that is why we haven't recieved this request before. There is a trick that you can use to quickly generate the CREATE TABLE statement. You can use the following option to get SAS to spit out the DDL (CREATE STATEMENT). You can use the PROC SQL NOEXEC option to just display the Teradata SQL without executing it.

OPTIONS SASTRACE=',,,d' SASTRACE=saslog NOSTSUFFIX;

You can copy the CREATE TABLE statement to an editor and do search/replace on the CHAR(n) columns. Once you have done that you can use SQL pass-thru to execute the SQL. Be sure to set a proper primary index or a no primary index (NOPI) table. This may be faster than using the DBTYPE= option.

If you need a full code example, please let me know.

Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.