BookmarkSubscribeRSS Feed

change default when writing from SAS into Teradata

Started ‎08-07-2013 by
Modified ‎10-05-2015 by
Views 1,361

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

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 received 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.

Version history
Last update:
‎10-05-2015 03:52 PM
Updated by:

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!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags