sas enterprise guide import csv file into relational database while the table already exists

Question

I want to use sas enterprise guide 8.3 import csv file into a relational database. I launch the import data wizard, locate the csv file in local folder(Windows OS), then find the library of the relational database. From the GUI, I cannot setup the mapping between the source and the target. the target is a table in relational database which has more fields than the csv file. The import data wizard failed because the wizard tried to replace the table which already exists.

so I cannot use import data wizard to achieve my goal? only with following code ?

Libname TD teradata userid=xxxx password=xxxx fastload=Yes
proc import 
        dbms=csv                    /* specify you load comma separated data */
        datafile="C:\temp\test.csv" /* fill in your data file                */
        out=TD;                    /* specify de dataset to load it to      */
      /*  replace;                   so TEST will not replaced if it exists */
run;

I am not sure the code above can solve my problem: out =TD does not specify the table name, just specify the target database. Thanks you very much!

Answer 1

It sounds like you are trying to append this .csv file to an existing Teradata table using SAS. You can do this in two steps:

1. Import the .csv file into SAS as a temporary SAS table (alternatively, you can import this as a temporary table in Teradata if you'd like)
2. Append the temporary SAS table to the master Teradata table

The SAS Teradata Access Engine will handle all the translation of code from one database to another:

Libname TD teradata userid=xxxx password=xxxx fastload=Yes;

proc import 
    dbms = csv                    
    file = "C:\temp\test.csv" 
    out  = tempTable;                    
    replace;                  
run;

proc append base = td.have
            data = tempTable
            force;
run;

Or you could use SQL to append:

proc sql;
    insert into td.have
        select * from tempTable
    ;
quit;