topic Re: Need help with Proc SQL Cast in SAS Programming

Need help with Proc SQL Cast

ChickenLittle — Mon, 27 Feb 2023 22:36:23 GMT

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: !, !!, &, ),

Re: Need help with Proc SQL Cast

SASKiwi — Mon, 27 Feb 2023 22:43:02 GMT

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.

Re: Need help with Proc SQL Cast

PaigeMiller — Mon, 27 Feb 2023 22:49:02 GMT

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;

Re: Need help with Proc SQL Cast

ChickenLittle — Mon, 27 Feb 2023 22:47:55 GMT

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

Re: Need help with Proc SQL Cast

PaigeMiller — Mon, 27 Feb 2023 22:48:30 GMT

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

Re: Need help with Proc SQL Cast

ChickenLittle — Mon, 27 Feb 2023 22:50:22 GMT

Thanks so much!

Re: Need help with Proc SQL Cast

ChickenLittle — Mon, 27 Feb 2023 23:01:08 GMT

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