topic Re: Using NULLCHAR with Teradata? in SAS Programming

Using NULLCHAR with Teradata?

TimS — Wed, 21 Dec 2011 00:40:41 GMT

I'm pretty (very) new to SAS, so this may be something simple that I'm missing...

I've built a data set that I now want to insert into a Teradata table. I also want to insert NULLs for any missing values. So, I found the NULLCHAR option (http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371592.htm) sounds like exactly what I want: if I set it to "YES" it "indicates that missing character values in SAS data sets are treated as NULL values if the DBMS allows NULLs. Otherwise, an error is returned."

I'm trying to figure out if I'm using it incorrectly...because my table in Teradata is still showing blanks instead of NULLs. The code below is not giving me any errors/warnings, and since I'm not really sure what I'm doing, I've tried it in other places in my code, and they all gave me syntax errors. So while it's not giving me any errors, it's not really having any effect. (I'm on Teradata v13.10.02.08 and SAS 9.2, btw).

Here's my code...am I using this incorrectly?

proc append base=td.mytable (nullchar=yes fastload=yes TPT=yes TPT_MAX_SESSIONS=8 TENACITY=6 SLEEP=1)

DATA=mydataset force;

run;

Let me know if I need to provide more info. And thanks in advance.

Using NULLCHAR with Teradata?

Tom — Wed, 21 Dec 2011 00:55:28 GMT

How are you looking at the Teradata table? If you are looking at it from the SAS side then it has already been converted back to blanks on the way back out of the database.

Re: Using NULLCHAR with Teradata?

TimS — Wed, 21 Dec 2011 05:00:06 GMT

Hmmm...I'm not sure what you mean by looking at the Teradata table. My libname statement looks like this if that's what you mean:

libname td teradata user="&username." password="&password." database="&db." server="&servername" logdb=spool_reserve;

What my script is doing:

1. Create a SAS data set using passthrough SQL to Teradata

2. Delete all data from the Teradata target table

3. Insert the data set to the Teradata target using the SAS in my first post

I was hoping the NULLCHAR option would convert the missing values to NULLs on the 3rd step - I should say I'm open to other ways of accomplishing this. (Doesn't have to be NULLCHAR!)

More code below - does that help? Thanks again for your help.

proc sql;

connect to teradata (user="&username" password="&password" database="&db" server="&servername" mode=teradata);

CREATE table mydataset as

select * from connection to teradata

(

SELECT yada yada yada from various tables

disconnect from teradata;

quit;

proc sql;

connect to teradata (user="&username" password="&password" database="&db" server="&servername" mode=teradata);

execute(delete from "&database.".mytable) by teradata;

quit;

proc append base=td.mytable (nullchar=yes fastload=yes TPT=yes TPT_MAX_SESSIONS=8 TENACITY=6 SLEEP=1)

DATA=mydataset force;

run;

Using NULLCHAR with Teradata?

Ksharp — Wed, 21 Dec 2011 07:51:37 GMT

After check the documentatin about Teradata.

To control how SAS missing character values are handled by Teradata, use

the NULLCHAR= and NULLCHARVAL= data set options.

By default, Teradata columns accept NULL values. However, you can define columns

so that they do not contain NULL values. For example, when you create a SALES

table, define the CUSTOMER column as NOT NULL, telling Teradata not to add a row

to the table unless the CUSTOMER column for the row has a value. When creating a

Teradata table with SAS/ACCESS, you can use the DBNULL= data set option to

indicate whether NULL is a valid value for specified columns.

Using NULLCHAR with Teradata?

TimS — Wed, 21 Dec 2011 16:05:19 GMT

Ksharp wrote:

After check the documentatin about Teradata.

To control how SAS missing character values are handled by Teradata, use

the NULLCHAR= and NULLCHARVAL= data set options.

Yes, those are the options I'm trying to use - but I can't get them working, unless I'm using NULLCHAR incorrectly.

Using NULLCHAR with Teradata?

Tom — Wed, 21 Dec 2011 18:08:21 GMT

What are you actually trying to do? Do you have an application where this is an issue or are you just exploring how to work with Teradata?

In Teradata you can have a character variable that is NULL and that value is a different value than all blank characters.

In SAS there is no way to make that distinction.

If you are just working with the data from the SAS side using a libref then even if you succeed in getting Teradata to store NULL it wouldn't make any difference to you.

To really test what is happening you will need to use pass-through SQL .

Using NULLCHAR with Teradata?

TimS — Thu, 22 Dec 2011 20:01:23 GMT

You know what...today we completed an upgrade in our SAS environment (specifically, how we connect to Teradata) and this option magically started working. So I think the syntax I was using was correct, and it was some configuration issue then. Thanks to all.