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.

Solution

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.