DATA Step, Macro, Functions and more

Column not Accepted when Creating Oracle Table from SAS Dataset

Accepted Solution Solved
Reply
Valued Guide
Posts: 613
Accepted Solution

Column not Accepted when Creating Oracle Table from SAS Dataset

[ Edited ]

When I try to write a SAS dataset from SAS to Oracle I receive the error message:

 

ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-00907: missing right parenthesis..

This might be due to the column names that I am trying to import.  Oddly there is no ‘(‘ in the column names. 

 

The list of column names is attached.

 


Accepted Solutions
Solution
‎02-15-2018 01:54 PM
PROC Star
Posts: 508

Re: Column not Accepted when Creating Oracle Table from SAS Dataset

Posted in reply to DavidPhillips2

try this one last time PRESERVE_COL_NAMES = Yes

View solution in original post


All Replies
PROC Star
Posts: 508

Re: Column not Accepted when Creating Oracle Table from SAS Dataset

Posted in reply to DavidPhillips2

try options validvarname = any;

Valued Guide
Posts: 613

Re: Column not Accepted when Creating Oracle Table from SAS Dataset

[ Edited ]
Do use at the top of the script, like?
 
options validvarname = any;
libname ods oracle user=xxx password="xxxx"  path=ods1 schema=xxxx dbserver_max_bytes=1;
PROC Star
Posts: 508

Re: Column not Accepted when Creating Oracle Table from SAS Dataset

Posted in reply to DavidPhillips2

ya. try that

Valued Guide
Posts: 613

Re: Column not Accepted when Creating Oracle Table from SAS Dataset

Same error.  I think Oracle is more restrictive than SAS, looking into the restriction is tricky.

Solution
‎02-15-2018 01:54 PM
PROC Star
Posts: 508

Re: Column not Accepted when Creating Oracle Table from SAS Dataset

Posted in reply to DavidPhillips2

try this one last time PRESERVE_COL_NAMES = Yes

Valued Guide
Posts: 613

Re: Column not Accepted when Creating Oracle Table from SAS Dataset

The below worked:

 

proc sql;
create table ods.OPDS_IPEDS_MULTI_INST (PRESERVE_COL_NAMES = Yes) as
select * from yearsCombined;
quit;

 

Thanks for your help with this.  I having a hard time researching this one.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 110 views
  • 0 likes
  • 2 in conversation