SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

convert varchar to number in a proc sql

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

convert varchar to number in a proc sql

hello,

I have a problem in my sql sas procedures; I do a join between two tables to retrieve a field with a "case When "

proc sql;

select

CASE WHEN A.SOUR in ('BIO') then G.FACT else F.FACT end as FACT

the problem is that the attributes of the two fields are different in the two tables (G and F); I have a number and a varchar; This column will automatically take a varchar format  and I want it to be number because later in my sql procedures I will join this field with the same but with a number format on other tables.

I tried an update on the table but I get an error: Bad character in size

If someone has an idea ..

Thank you in advance


Accepted Solutions
Solution
‎07-10-2017 10:04 AM
Regular Contributor
Posts: 180

Re: convert varchar to number in a proc sql

[ Edited ]

You have to convert the char variable to number.

 

If G.FACT is varchar then use:

  

CASE WHEN A.SOUR in ('BIO') then input(G.FACT,best12.) else F.FACT end as FACT

 

or If F.FACT is varchar then use:

  

CASE WHEN A.SOUR in ('BIO') then G.FACT else input(F.FACT,best12.) end as FACT

 I used best12. as the informat in the example but you should use the correct one.

 

CTorres

 

Editor's note: if the query is running via PROC SQL CONNECT clause and pushing down to Teradata, you'll need to use Teradata syntax to convert the values.  Try CAST(F.FACT as NUMBER) to convert a char to number.

View solution in original post


All Replies
Solution
‎07-10-2017 10:04 AM
Regular Contributor
Posts: 180

Re: convert varchar to number in a proc sql

[ Edited ]

You have to convert the char variable to number.

 

If G.FACT is varchar then use:

  

CASE WHEN A.SOUR in ('BIO') then input(G.FACT,best12.) else F.FACT end as FACT

 

or If F.FACT is varchar then use:

  

CASE WHEN A.SOUR in ('BIO') then G.FACT else input(F.FACT,best12.) end as FACT

 I used best12. as the informat in the example but you should use the correct one.

 

CTorres

 

Editor's note: if the query is running via PROC SQL CONNECT clause and pushing down to Teradata, you'll need to use Teradata syntax to convert the values.  Try CAST(F.FACT as NUMBER) to convert a char to number.

New Contributor
Posts: 3

Re: convert varchar to number in a proc sql

thanks but i tried this and i get an error : "

Syntax error: expected something between the 'then' keyword and the 'input'

       keyword. "

I have another question please ; which format should i apply if the column  is an integer in teradata ?

thanks

Super User
Posts: 7,771

Re: convert varchar to number in a proc sql

There is no varchar in SAS. SAS only knows character (fixed length) and numeric.

data have;

format x y 12. z $12.;

input x y z;

cards;

1 3 3

2 3 5

;

run;

proc sql;

create table want as

select

  x,y,z,

  case

    when x = 1

    then input(z,best12.)

    else y

  end as newvar

from have;

quit;

works here.

Could you post log of the SQL that ended with the syntax error?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: convert varchar to number in a proc sql

Posted in reply to KurtBremser

thanks Kurt ! what do you mean by log ?

I'm working with many tables in teradata using proc sql everytime ; so I don't use "data" syntax .

When i tried  : CASE WHEN A.SOUR in ('BIO') then input(G.FACT,best12.) else F.FACT end as FACT  , i get this error :

ERROR: Teradata execute: Syntax error: expected something between the 'then' keyword and the 'input'

       keyword.

G.FACT is a varchar 50 in teradata and F.FACT is an integer and I want G.FACT as an integer in my proc sql in SAS

I expect that it's more clear

Super User
Posts: 7,771

Re: convert varchar to number in a proc sql

Every time you run SAS code, you get a log either in the log window (base SAS) or in a log tab (Enterprise Guide).

But see here:

ERROR: Teradata execute

You do not have a SAS problem, you have a Teradata problem. I suspect that Teradata has a different syntax concerning the input keyword, and this part of the SQL is actually handled at the Teradata side.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 7499 views
  • 1 like
  • 3 in conversation