DATA Step, Macro, Functions and more

create teradata schema from sas dataseet

Reply
Frequent Contributor
Posts: 110

create teradata schema from sas dataseet

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!

PROC Star
Posts: 326

Re: create teradata schema from sas dataseet

Posted in reply to csetzkorn

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;


Frequent Contributor
Posts: 110

Re: create teradata schema from sas dataseet

Thanks that's what I thought ... and thanks I know how to do all this in sas.
Frequent Contributor
Posts: 110

Re: create teradata schema from sas dataseet

[ Edited ]

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
PROC Star
Posts: 326

Re: create teradata schema from sas dataseet

Posted in reply to csetzkorn

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

Frequent Contributor
Posts: 110

Re: create teradata schema from sas dataseet

I see - sorry. Thanks!
Ask a Question
Discussion stats
  • 5 replies
  • 89 views
  • 1 like
  • 2 in conversation