Inserting Data into Teradata using Proc SQL

Question

I have to insert data into Teradata from SAS EG. I am using Proc SQL for this.

Data insertion is happening but with wrong date.

I have tried different formats but still date is coming as 01-01-1960

Proc Sql;
insert into teradata (emailid,status,delivery_date) select emailid,status,delivery_date from table;
quit;

Notice that date inserted into teradata as 01-01-1960. But the actual date was 11-Feb-2018.

Writing SAS dates are a pain in the behind. You might find some pointers in prior thread: stackoverflow.com/questions/53781236/… — pinegulf
Is that the actual code ? into teradata would be inserting into a SAS data set in the work library. — Richard

Richard Richard · Accepted Answer · 2019-02-11T12:44:57

Try (untested)

Proc Sql;
  insert into teradata (emailid,status,delivery_date)
  select 
      emailed
    , status
    , ( delivery_date - '01JAN1970'D ) * 86400
  from
    table
  ;
quit;

or

%macro SAS_D_to_TERA_TS (sas_date);
  ( &sas_date - '01JAN1970'D ) * 86400
%mend;
… 
  , %SAS_D_to_TERA_TS ( delivery_date )

SAS DATETIME values are number of decimal seconds since 01-JAN-1960.
SAS DATE values are number of days since 01-JAN-1960.
Teradata TIMESTAMP values are number of decimal seconds since 01-JAN-1970.

DATETIME and TIMESTAMP have the same units albeit different origins (epoch)

The conversion from a SAS date value to a Teradata timestamp value requires

Translating the SAS date value to the Teradata epoch as a SAS date
- - '01-JAN-1970'D
Scaling the translated date value to a datetime (aka timestamp) value.
- * 86400 (seconds in a day)

Date values when miscast as DateTime values

data _null_;    
  delivery_date = '11FEB2018'D;

  put delivery_date= MMDDYY10.;
  put delivery_date= DATETIME.;

  delivery_date_dt = delivery_date * 86400;
  put delivery_date_dt= DATETIME.;

  sas_date_epoch = '01JAN1960'D;
  put sas_date_epoch=;

  teradata_date_epoch = '01JAN1970'D;
  put teradata_date_epoch= 'days after SAS epoch';
run;
----------- LOG -----------
delivery_date=02/11/2018
delivery_date=01JAN60:05:53:46
delivery_date_dt=11FEB18:00:00:00
sas_date_epoch=0
teradata_date_epoch=3653 days after SAS epoch

Inserting Data into Teradata using Proc SQL

1 Answers