Need help with Proc SQL Cast

[ChickenLittle]

Hi, I'm trying to convert a "RefNumber," which is a char, into an integer inside a Proc SQL statement. Is that possible? I am getting an error when I run the code below.

Proc SQL;
Create Table Temp as
Select a.*,
cast(b.RefNumber as decimal(15,0)) as KeyRef
From TableA a
Left Join TableB b on a.KeyRef = b.KeyRef;
quit;

Error Message: ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ),

[SASKiwi]

Why? If you aren't doing calculations on RefNumber leave it as character. Also CAST is not a valid function in SAS SQL. CAST is a function used in some external DBMS SQL implementations like Oracle. 

[ChickenLittle]

Hi, I need it to be a numeric value because the RefNumber in the other table is numeric.

[PaigeMiller]

---

@ChickenLittle wrote:
Hi, I need it to be a numeric value because the RefNumber in the other table is numeric.

---

Actually this is backwards. You need to convert the numeric version in the other table to character.

[ChickenLittle]

Yup, I got that part, I just needed the "input(...)" statement to realize what I needed, thanks so much!

[PaigeMiller · Accepted solution]

From now on, when you get an error in the log, show us the ENTIRE log for that PROC or DATA step. DO not show us small portions of the log, we need to see the ENTIRE log for that PROC or DATA step.

Proc SQL;
    Create Table Temp as
    Select a.*,
    b.KeyRef as keyref2
    From TableA a
    Left Join TableB b on put(a.KeyRef,15.0) = b.KeyRef;
quit;

[ChickenLittle]

Thanks so much!