topic Re: Generic script to pick datepart of datetime field and insert into tables in SAS Programming

Generic script to pick datepart of datetime field and insert into tables

Hercules — Fri, 28 Nov 2014 06:07:35 GMT

Ok, So Thank you SAS community and specially Reeza who helped me to built a generic script to populate 60 tables at one go.

Now, I want to insert datepart of a datetime field from oracle to teradata. So the logic would be:

%macro

Insert into tera.&table_name (some columns, datepart(datetime_field)) select * from ora.&table_name;

%mend

and picking table from a dataset.

Now this should be parameterised.

Please help!

Re: Generic script to pick datepart of datetime field and insert into tables

jakarman — Fri, 28 Nov 2014 11:08:30 GMT

Use the approaches for loading data to Teradata as documented by them. You are on the paths of other approaches.

For dates. SAS is using numbers since 1 jan 1960 and Teradata is using them since 1 jan 1900. All conversions should be easily run when having set correct options.

It is based on the known formats associated with the SAS variables.

The pitfalls:

- take attention to numeric precision (the famous 15/12 digits)

- with Teradata 14 the table names are limited to 30 whereas SAS is going to 32.

- As Teradata is designed for mass-processing is will not give always errors on every record processing.

Sometimes duplicates and other erroneous records are placed in special datasets (adding 3 letters to the table) and everything is marked ok.

- There are mote datatypes in Teradata as in SAS (as usual). Conversion could make sense

Do you do some cooperation with a Teradata DBA?

Re: Generic script to pick datepart of datetime field and insert into tables

Hercules — Sat, 29 Nov 2014 05:07:59 GMT

You didn't get me Jaap.

I have a structure made in teradata for a table.

For example, I have a table dummy_tera having a column start_date as date column.

Now I have another table dummy_ora having same structure as dummy_tera with data but only difference is that it is timestamp datatype.

Now if I apply datepart(start_date) and then insert it into Teradata table using

proc sql;

Insert into... Select * from...

THEN IT WORKS PERFECTLY FINE, otherwise it give an error that SAS VALUE CANNOT BE CONVERTED INTO TERADATA DATE.

Now I made a generic script which rather a macro which can load as many table as anyone want at a time which do not have date columns.

I want to optimize my script so that it can handle date columns as well. So that It can apply datepart automatically whenever it searches a date type field.

For that I need an idea.

Re: Generic script to pick datepart of datetime field and insert into tables

jakarman — Sat, 29 Nov 2014 08:56:44 GMT

Teradata is different as to other databases. I made the notes on that at: https://communities.sas.com/message/239871#239871

The SAS/Access modules are doing the conversions based on the associated sas-formats

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition (oracle) and SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition
You could use a proc copy from SAS with one side Oracle and at the other Teradata. That is the most easy way of thinking.

Using this with SAS dataset will work as the formats off all SAS variables are known when run.

There are two issues:

- going from Oracle to Teradata will have the effect of not using SAS tables and by that probably missing the SAS formats.

That is why that direct copy can have issues.

- Storing tables in Teradata you must deal with that distributed approach there (nodes/amps). Choosing a "primary key" is MANAGING THAT DISTRIBUTION.

That is why you should first define a table in Teradata (with primary key) and than load that using append with TERADATA OPTIONS.

When you understand this and is working fine you can improve that for a repeatable action.

When you are having dates remember they are all technically different on all technically system although they are looking equal.

SAS is converting the dates for you using the mentioned approach. It is coming from several sources (skills Teradata , sas/access).

There are exceptions on dates that cannot be converted.

- The Gregorian calendar started in 1580 or something like that. Dates before that are assumed to be invalid.

- Some business analysts introduced faked dates as some special case. When it is technical a string a DBMS accepted that. Neither SAS or Teradata will. 30-februari 32-may they are never valid.

- SAS is having the missing concept. Dates can be missing in SAS . This missing number does not exist in a DBMS. They are using an NULL concept (three value logic) for not being there.

This is something of doing dataquality. When there is rubbish in the old data than it is rubbish even when that was technically accepted.

Re: Generic script to pick datepart of datetime field and insert into tables

Hercules — Sun, 07 Dec 2014 10:41:48 GMT

ACHIEVED!!!

I have written a script which can insert into n number of tables of teradata, source as Oracle.

Also it will add audit columns, surrogate key and will pick datetime columns from oracle and insert into date columns of teradata table..

All can be done using only one script.