BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nid197
Obsidian | Level 7
How do i change the length of multiple variables and datatype of variables from numeric to character in sas teradata passthrough sql.

Please check and suggest if the below syntax is correct
E.g-
proc sql;
connect to teradata as tera1 (user=myuser password=mypass server=myserver
tpt=yes fastexport=yes);
create table mytable as
select * from connection to tera1
(select age,entityno
,'fbu1' as put(segid) length=10,
trnsid as put(profgrp_id) length=$30.,accno length=20,startdt length=10,enddt length=10 from mytable
where age > 15 );
disconnect from tera1;
quit;

Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
nirajs
Fluorite | Level 6

you can assign length in Teradata using decimal(10,0) but the SAS dataset you created will still have the length of 8 bytes.

View solution in original post

8 REPLIES 8
Reeza
Super User
Anything within the parenthesis must be Teradata SQL, not SAS SQL.

If you want to change the types, you can do some of it in the location where you currently are selecting *. Instead of * place the fields and specifications needed.

Your method for specifications is wrong though, it should be

select age length=$30. format=$30., put(segid) as fbu1 length=$30. format=$30.

https://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n1spf244gkq54cn13bbiy307hknr.htm&docset...
nirajs
Fluorite | Level 6

The data functions will not work while you are running the code in Teradata using sql pass through. The belw should should give you the desired output. Let me know if this works.

proc sql;
connect to teradata as tera1 (user="xxxxxx" password="xxxxxxx" server="xxxxx"
tpt=yes fastexport=yes);
create table mytable as
select * from connection to tera1
(select age
        ,entityno
        ,cast(segid as varchar(10)) fbu1
        ,cast(profgrp_id as varchar(30)) trnsid
        ,cast(accno as varchar(20))accno
        ,cast(startdt as varchar(10)) startdt
        ,cast(enddt as varchar(10)) enddt
from mytable
where age > 15 );
disconnect from tera1;
quit;

 

nid197
Obsidian | Level 7
Yes you are right length and format statements are not working in teradata passthrough but i dont know why it is giving me problem when i try to use cast to convert character as integer(10).
Cant i specify length.
It gives me error as
Syntax error:expected something between 'integer' and '('.
Please help
nirajs
Fluorite | Level 6

You don't have to specify length to the numeric columns.

 

In SAS, the default length of a numeric variable is 8 bytes. . The limit is NOT 8 digits but 8 bytes. 8 bytes means we can store up to 16 digits for a numeric variable in SAS.

nirajs
Fluorite | Level 6

you can assign length in Teradata using decimal(10,0) but the SAS dataset you created will still have the length of 8 bytes.

Reeza
Super User
SQL isn't a single thing, there are different versions for each application. Since you're crossing applications here, Teradata and SAS you're actually using two different SQL syntaxes. Everything within the parenthesis/brackets must be Teradata SQL. Everything outside of the parenthesis is SAS SQL. SAS Functions such as PUT() will not work within the parenthesis.
nid197
Obsidian | Level 7
@nirajs thankyou for your solution it worked.i am able to change the datatypes and length.but now when i create a new dataset i see a " . " added to the variables data (Eg. 35258. , 79843. , 85246. ) of which i had changed the datatype.is it because the the datatype i gave? Which is "varchar" and there is no character values(only numeric data) but i want it to be char?what is issue can you please help.i hope you undestood my explanation.
nid197
Obsidian | Level 7
@Reeza thankyou so much:)

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 20274 views
  • 4 likes
  • 3 in conversation