BookmarkSubscribeRSS Feed
csetzkorn
Lapis Lazuli | Level 10

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!

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

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;


csetzkorn
Lapis Lazuli | Level 10
Thanks that's what I thought ... and thanks I know how to do all this in sas.
csetzkorn
Lapis Lazuli | Level 10

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
kiranv_
Rhodochrosite | Level 12

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

csetzkorn
Lapis Lazuli | Level 10
I see - sorry. Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1036 views
  • 1 like
  • 2 in conversation