Load SAS dataset into Teradata table using Fast LOAD

Question

I am trying to load SAS dataset into a teradata table using FASTLOAD utility. This works fine in some cases, but I want to separate the error tables and create them in my own/other database in teradata environment.

Could some one provide me the syntax (I do know it but it's not working) for how to make it possible?

Any method is fine either using proc sql command or proc append command. Thanks in advance.

Safe to assume you have tried specifying your alternate database for the log tables as a prefix to the tablename and that didn't work? — Rob Paller
Hello Rob, yes i been defining the log tables adding other database name as a prefix. could you let me know how to get this issue solved. — lucky
Can you post what you have tried that is and isn't working? (Rename objects if you need to - the syntax is what is important.) — Rob Paller
Rob thanks for you reply.here is the syntax that fails for me proc append base=tdserv.loadtest (TPT=YES FASTLOAD=YES TPT_ERROR_TABLE_1 = 'TPTLOAD.LOADTEST_ET' TPT_ERROR_TABLE_2 = 'TPTLOAD.LOADTEST_UV' TPT_LOG_TABLE = 'TPTLOAD.LOADTEST_RS') data=work.loaddata; run; — lucky
Rob thanks for you reply.here is the syntax that fails for me , 'tdserv' is my target database and TPTLOAD is my database where i want to create my error tables. here is the syntax that fails LIBNAME TDServ TERADATA SERVER=TDServ USER=tduser PASSWORD=tdpasswd1; proc append base=tdserv.loadtest (TPT=YES FASTLOAD=YES TPT_ERROR_TABLE_1 = 'TPTLOAD.LOADTEST_ET' TPT_ERROR_TABLE_2 = 'TPTLOAD.LOADTEST_UV' TPT_LOG_TABLE = 'TPTLOAD.LOADTEST_RS') data=work.loaddata; run; — lucky

BellevueBob BellevueBob · Accepted Answer · 2013-03-31T14:44:59

You can use the LOGDB libname option to tell SAS into which database the log files should be created. By default they are created in the same database as the table being created (named as the target table named plus the three character suffixes you've discovered). Using the info provided in your comments, try this:

/* Delete any exisiting log files for table TPT_LD_TEST */
libname TPTLOAD TERADATA 
                SERVER=TDServ DATABASE=TPTLOAD 
                USER=tduser PASSWORD=tdpasswd1
                ;
proc delete data=TPTLOAD.TPT_LD_TEST_ET;
run;
proc delete data=TPTLOAD.TPT_LD_TEST_UV;
run;
proc delete data=TPTLOAD.TPT_LD_TEST_RS;
run;
libname TPTLOAD clear;

/* Define connection to target database */
LIBNAME TDSERV  TERADATA 
                SERVER=TDServ 
                USER=tduser PASSWORD=tdpasswd1
                LOGDB=TPTLOAD;

/* Truncate target table if necessary */
proc sql noprint;
   delete from TDSERV.TPT_LD_TEST;
quit;

proc append base=TDSERV.TPT_LD_TEST(fastload=yes tpt=yes)
            data=work.FastLoad;
run;

I added some code to delete any existing rows in the target table (a requirement for FASTLOAD).

If you have DROP TABLE and CREATE TABLE rights on your target database, it might be safer to drop and re-create the table so you can guarantee the structure and explicitly name the table index.

/* Delete target table if it exists */
proc delete data=TDSERV.TPT_LD_TEST;
run;

data TDSERV.TPT_LD_TEST 
            (fastload=yes tpt=yes
             dbcreate_table_opts='primary index(index_column_name)'
            )
   set work.FastLoad;
run;

And in either case, be sure to remove any duplicate records from your source dataset; those will be written to your error files (as well as any records that fail other constraints).

PROC DELETE is a handy device because it will not create an error if the target table does not exist.

Load SAS dataset into Teradata table using Fast LOAD

1 Answers