topic Re: create teradata schema from sas dataseet in SAS Programming

create teradata schema from sas dataseet

csetzkorn — Fri, 03 Nov 2017 13:20:02 GMT

Hi all,

Is there a simple way to produce the required teradata schema given a sas dataset and its (in)formats or do I have to do this manually?

For example, $CHAR82 may require CHAR(82) CHARACTER SET LATIN NOT CASESPECIFIC etc.

Thanks!

Re: create teradata schema from sas dataseet

kiranv_ — Fri, 03 Nov 2017 13:52:06 GMT

It is not great idea to make a char(82) in any DBMS. Datatypes you can control in dataset option dbtype. Below is an example picked up from http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371576.htm and it shows how you create datatypes you want. SAS formats are not understood by any DBMS and will not be written to DBMS.

data mydblib.newdept(dbtype=(deptno='byteint' city='varchar(25)'));
set dept;
run;

But if you want to create Teradata schema, best way is to use explicit pass through as show below

proc sql;   
connect to teradata (server=myserver user=myuserid pw=mypass); 
   execute(create table teraschema.employee 
    (cust_id decimal(10, 0),  
    cust_fname varchar(40),
 cust_lname varchar(50))  
 primary index(cust_id)) by teradata;   
  execute(commit) by teradata; 
   disconnect from teradata; 
 quit;

Re: create teradata schema from sas dataseet

csetzkorn — Fri, 03 Nov 2017 14:11:59 GMT

Thanks that's what I thought ... and thanks I know how to do all this in sas.

Re: create teradata schema from sas dataseet

csetzkorn — Fri, 03 Nov 2017 14:21:03 GMT

Btw I am getting:

ERROR 22-7: Invalid option name DBTYPE.

when I try something along those lines:

data Work.Test(dbtype=(x='int' y='varchar(5)'));
set Work.Bla;
run

Re: create teradata schema from sas dataseet

kiranv_ — Fri, 03 Nov 2017 14:28:00 GMT

work.test is not right that should be Teradata libname

libname yes teradata user='user' pass='passd' tdpid=prodserver ;

data yes.Test(dbtype=(x='int' y='varchar(5)'));
set Work.Bla;
run

Re: create teradata schema from sas dataseet

csetzkorn — Fri, 03 Nov 2017 14:37:08 GMT

I see - sorry. Thanks!