BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anare
Calcite | Level 5

Hi All,

 

I have one dataset created in SAS which has numeric, character and date variable. when i tried to put in oracle library which i created am getting below error.

 

"Error attempting to CREATE a DBMS table. ERROR. ORACLE execute error: ORA-00904 invalid identifiers it showing.

 

Any help it will be very much hepful.

 

Thanks !!!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Note that VIEW is also probably an illegal name in Oracle.

You could try setting the PRESERVER_COL_NAMES option and see if it causes SAS to generate the SQL code using double quotes around the variable names.

 

If you are creating a table in PROC SQL, you must also include the 
PRESERVE_COL_NAMES=YES option in your LIBNAME statement. 
Here is an example.

libname mydblib oracle user=myusr1 password=mypwd1
        preserve_col_names=yes;
   proc sql dquote=ansi;
      create table mydblib.mytable ("my$column" int);

View solution in original post

10 REPLIES 10
PoojaP1
Fluorite | Level 6

Hi there,

 

It would be helpful to understand what statement you actually executed, and how did you create the Oracle libref.

 

 

LinusH
Tourmaline | Level 20

Also, PROC CONTENTS of your input data set and a DESCRIBE TABLE or similar for the Oracle target table would be helpful.

Data never sleeps
anare
Calcite | Level 5

Thanks you for reply and i have created a oracle library using below code;

Libname TEST oracle user = *** PW= *** path=server buffsize=5000;

run;

 

Library is created successfully.

 

Now i have created imported a table from local system and am trying to load the data to Oracle server.

 

Data Test;

Set work.products;

run;

 

In the above code Test is oracle Lib and dataset am taking it from sas work lib. when i execute the code am getting an error stating that unidentifer 

 

ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-00904: : invalid identifier..

 

your solution will helpfull.

 

 

PoojaP1
Fluorite | Level 6

When you are writing your data step, you are actually creating a data set in some library which you have defined or the work library (by default).

 

In the following statement you have not named your dataset, you have only named the library.

Data Test;

 

Corrected form would be a dataset having a two level name as - 

 

Data Test.mydata;

set products;

run;

 

(Assuming the new dataset's name is mydata).

anare
Calcite | Level 5

Hi Pooja,

 

Now i have created the below code and am getting same error.

 

Data Test.Mydata;

set APMD.Products;

Run;

 

Here Test is a oracle lib and dataset am creating is mydata, Taking the data form SAS lib APMD dataset products.

 

Same error it is showing below are the data types.


Date Num 8 MMDDYY10. MMDDYY10.
Week_nbr Num 8 BEST2. BEST2.
View Char 200 $CHAR20. $CHAR20.
Geo Char 124 $CHAR5. $CHAR5.
LOB Char 124 $CHAR11. $CHAR11.
Page Char 200 $CHAR26. $CHAR26.
Volume Num 8 BEST8. BEST8.
Average Num 8 BEST12. BEST12.
95% Num 8 BEST12. BEST12.
Start_Date Num 8 MMDDYY10. DATE9.
End_Date Num 8 MMDDYY10. DATE9.
From_Date-To_Date Char 21 $CHAR21. $CHAR21.
SLR Num 8 BEST12. BEST12.

Thanks

Sven111
Pyrite | Level 9

I think the problem is in your variable names in the APMD.Products table.  One of the columns starts with a number, which Oracle doesn't allow.  Oracle column names generally have to start with an alpha character (or possibly an underscore, I don't recall).  Try the code @PoojaP1 recommended after changing the column name from "95%" to something starting with an alpha character (and possibly removing the % sign as well.)

 

You also may want to review this document on the Oracle Libname engine, I've found it useful as I deal with SAS-Oracle stuff on a regular basis.  In particular, making sure you're using some of the SASTRACE options is very helpful in debugging stuff.

 

Patrick
Opal | Level 21

As @Sven111 states you need to use variable names compliant with Oracle naming standards; 95% is not such a name and you also shouldn't use a dash in your names.

 

Almost all variable names which comply with SAS naming standards will also comply with Oracle naming standards.

Unlike with SAS Oracle doesn't allow you to use names for variables which are also keywords. For this reason also variable name DATE is most likely not a valid variable name for Oracle.  

 

/* only allow SAS variable names which comply with SAS naming standards */
options validvarname=v7;

/* show in SAS log which SQL the SAS/Access engine generates to push processing to the database */
options sastrace=',,,d' sastraceloc=saslog nostsuffix;  

/* Append data to Oracle table (creates Oracle table if it doesn't exist */
proc append base=Test.Mydata data=APMD.Products;
run;quit;

As for numeric SAS variables containing a SAS date or datetime value:

The only information the SAS/Access engine has that such values need to map into an Oracle variable of type DATE and not of type NUMERIC is the SAS format assigned to the numeric SAS variable. I've made the experience that the SAS/Access engine doesn't recognise all SAS formats and I'm therefore always assigning a format of Date9. or Datetime21. in order to ensure that things work as desired.

anare
Calcite | Level 5

Hi All,

 

Thanks a lot for your reply. but still am getting some erorr after following solution which provided

 

MDTD is a Oracle lib and trying to create real_data from SAS local library AMD.real_data dataset. After executing am getting below error.

 

30 Data MDTD.real_data;
31 set amd.real_data;
32 run;


ORACLE_14: Prepared: on connection 1
SELECT * FROM REAL_DATA

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

ORACLE_15: Executed: on connection 2
CREATE TABLE REAL_DATA(num DATE,Week_nbr NUMBER ,View VARCHAR2 (800),Geo VARCHAR2 (496),LOB VARCHAR2 (496),Page VARCHAR2
(800),Volume NUMBER ,Average VARCHAR2 (44),Percent VARCHAR2 (32),Start_Date DATE,End_Date DATE,Fromto VARCHAR2 (84),SLR VARCHAR2
(16),Received DATE)

ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-00904: : invalid identifier..
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set MDTD.REAL_DATA may be incomplete. When this step was stopped there were 0 observations and 14 variables.
ORACLE: *-*-*-*-*-*-* ROLLBACK *-*-*-*-*-*-*
ERROR: ROLLBACK issued due to errors for data set MDTD.REAL_DATA.DATA.
NOTE: DATA statement used (Total process time):

 

 

Could you please assist on this what has to be done to create DBMS table 

Tom
Super User Tom
Super User

Note that VIEW is also probably an illegal name in Oracle.

You could try setting the PRESERVER_COL_NAMES option and see if it causes SAS to generate the SQL code using double quotes around the variable names.

 

If you are creating a table in PROC SQL, you must also include the 
PRESERVE_COL_NAMES=YES option in your LIBNAME statement. 
Here is an example.

libname mydblib oracle user=myusr1 password=mypwd1
        preserve_col_names=yes;
   proc sql dquote=ansi;
      create table mydblib.mytable ("my$column" int);
Patrick
Opal | Level 21

@anare

Besides of the potential issue with variable name VIEW what I'm normally doing for production worthy implementations, is to create the Oracle table explicitely via code (SQL pass-through).

To not have to type that much: Either pick-up the generated code from the log and amend or create the Oracle table directly via SQL Developer or the like and then copy the SQL from there.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 9301 views
  • 1 like
  • 6 in conversation