topic Re: Change datatype and length in proc sql in SAS Procedures

Change datatype and length in proc sql

nid197 — Mon, 27 Jan 2020 19:19:58 GMT

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

Re: Change datatype and length in proc sql

Reeza — Mon, 27 Jan 2020 19:40:04 GMT

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&docsetVersion=9.4&locale=en

Re: Change datatype and length in proc sql

nirajs — Mon, 27 Jan 2020 21:21:47 GMT

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;

Re: Change datatype and length in proc sql

nid197 — Tue, 28 Jan 2020 09:14:23 GMT

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

Re: Change datatype and length in proc sql

nirajs — Tue, 28 Jan 2020 14:41:53 GMT

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.

Re: Change datatype and length in proc sql

nirajs — Tue, 28 Jan 2020 14:48:35 GMT

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

Re: Change datatype and length in proc sql

Reeza — Tue, 28 Jan 2020 21:23:35 GMT

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.

Re: Change datatype and length in proc sql

nid197 — Wed, 29 Jan 2020 08:57:00 GMT

@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.

Re: Change datatype and length in proc sql

nid197 — Wed, 29 Jan 2020 08:57:35 GMT

@Reeza thankyou so much:)