topic Re: CAST function for Oracle in SAS DI 9.4 in SAS Programming

CAST function for Oracle in SAS DI 9.4

strsljen — Mon, 06 Nov 2017 13:25:11 GMT

Hi,

I am using SAS DI and trying to use Oracle CAST function to have integer result of division of one NUMBER attribute by 100.

Whatever I do, I get syntax error.

Tried:
(CAST ( {PERIOD_ID/100} AS INTEGER))

CAST(period_id/100 AS INTEGER)

etc ...

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, (, ), *, +, ',', -, ALL, BTRIM, CALCULATED, CASE, DISTINCT, EXISTS, INPUT, NOT, PUT, SUBSTRING,
TRANSLATE, UNIQUE, USER, ^, ~.

ERROR 200-322: The symbol is not recognized and will be ignored.

SAS code:
proc sql;
create table arpu01.WDPLPU as
select
PERIOD_ID,
LOAD_TYPE,
OWNER_CUSTOMER_KEY,
((CAST ( {PERIOD_ID/100} AS INTEGER))) as test1 length = 8
label = 'test1'
from &SYSLAST
;
quit;

The same goes for other options I tried.

I am doing something and I can't see it.

Any help is more than welcomed!

Thanks!

Best regards,

Mario.

Re: CAST function for Oracle in SAS DI 9.4

RW9 — Mon, 06 Nov 2017 14:02:03 GMT

This is not the DI forum subgroup, and as such I do not use it. However at a guess, I suspect your not actually connecting to an Oracle database, nor passing the SQL code through to one, correct? If so then you cannot use Oracle functions in the SQL, only ANSI SQL statements are valid and Base SAS functions. Therefore you would switch out cast() which is oracle only and use the SAS function int() (or round()):
((CAST ( {PERIOD_ID/100} AS INTEGER))) as test1 length = 8

int(PERIOD_ID/100) as test1 length = 8

Re: CAST function for Oracle in SAS DI 9.4

strsljen — Tue, 07 Nov 2017 07:29:55 GMT

Hi,

Thank you for the tip. That does the trick.

Best regards,

Mario.