DATA Step, Macro, Functions and more

SAS to Oracle dataset load

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

SAS to Oracle dataset load

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 !!!


Accepted Solutions
Solution
‎02-23-2017 06:44 AM
Super User
Super User
Posts: 7,039

Re: SAS to Oracle dataset load

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


All Replies
Contributor
Posts: 22

Re: SAS to Oracle dataset load

Hi there,

 

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

 

 

Super User
Posts: 5,426

Re: SAS to Oracle dataset load

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
Occasional Contributor
Posts: 13

Re: SAS to Oracle dataset load

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.

 

 

Contributor
Posts: 22

Re: SAS to Oracle dataset load

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).

Occasional Contributor
Posts: 13

Re: SAS to Oracle dataset load

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

Frequent Contributor
Posts: 83

Re: SAS to Oracle dataset load

[ Edited ]

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.

 

Respected Advisor
Posts: 4,173

Re: SAS to Oracle dataset load

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.

Occasional Contributor
Posts: 13

Re: SAS to Oracle dataset load

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 

Solution
‎02-23-2017 06:44 AM
Super User
Super User
Posts: 7,039

Re: SAS to Oracle dataset load

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);
Respected Advisor
Posts: 4,173

Re: SAS to Oracle dataset load

@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.

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 479 views
  • 0 likes
  • 6 in conversation