How to load data from SAS to Teradata where multiple excel files are imported in SAS with the Same column name

Question

I have 10 excel files with same column names that needs to be inserted into Teradata. I have created one table in Teradata with all column details and imported data into SAS . Now , how should I push from SAS to Teradata ? I need proc sql for this.

Kiran Kiran · Accepted Answer · 2018-10-15T15:31:37

If you have already created table in Teradata one way is to use proc append or proc sql by using libname. Fastload does bulk loading. An example is shown below

 libname teralib teradata server=server user=userid pwd=password ;
 libname saslib '/u/mystuff/sastuff/hello';

 proc append base= teralib.staging_customer_ref(fastload =yes)
 base = saslib.cust_ref;
   run;

or by using insert statement in Proc sql

    proc sql;
    insert into teralib.staging_customer_ref
    (FastLoad=YES)
   select * from saslib.cust_ref;
   quit;

please look into below paper, which discuses various options to move data from SAS to Teradata http://support.sas.com/resources/papers/EffectivelyMovingSASDataintoTeradata.pdf

Edit: Just read your question completly, if you want match names in insert statement( which works on position) probably you need to make a macro variable and use in insert statemnt and probably proc append is best in this scenario, as proc append on basis of names of columns

proc sql noprint;
select name into :cols separated by ','
 from Dictionary.columns
 where upcase(libname) = upcase('teralib')
  and upcase(memname) = upcase('staging_customer_ref');

   proc sql;
  insert into teralib.staging_customer_ref (FastLoad=YES)
  select &cols from saslib.cust_ref;

Edit2: looks like your both datasets are not same (i.e. datatypes are not same). you can take following steps

you can change DDL of Teradata table and do insert or you can.
do proc append with force option, so that you have null values at data mismatch(not recommended).
create table rather than insert(can be done using datastep or proc append) dbcreate_table_opts option is used to create appropriate primary index for a Teradata table.

point 1 is more preferable.

     proc sql;
    create table teralib.staging_customer_ref
    (FastLoad=YES dbcreate_table_opts= 'primaryindex(cust_number)') as
   select * from saslib.cust_ref;
   quit;

How to load data from SAS to Teradata where multiple excel files are imported in SAS with the Same column name

1 Answers