DATA Step, Macro, Functions and more

CAST function for Oracle in SAS DI 9.4

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

CAST function for Oracle in SAS DI 9.4

[ Edited ]

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.

--
Mario

Accepted Solutions
Solution
‎11-07-2017 02:29 AM
Super User
Super User
Posts: 9,599

Re: CAST function for Oracle in SAS DI 9.4

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

View solution in original post


All Replies
Solution
‎11-07-2017 02:29 AM
Super User
Super User
Posts: 9,599

Re: CAST function for Oracle in SAS DI 9.4

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

Contributor
Posts: 28

Re: CAST function for Oracle in SAS DI 9.4

Hi,

 

Thank you for the tip. That does the trick.

 

Best regards,

 

Mario.

--
Mario
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 252 views
  • 0 likes
  • 2 in conversation