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.
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
To
int(PERIOD_ID/100) as test1 length = 8
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
To
int(PERIOD_ID/100) as test1 length = 8
Hi,
Thank you for the tip. That does the trick.
Best regards,
Mario.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.