oracle,sas , How to update an Oracle Table from SAS efficiently?

How to update an Oracle Table from SAS efficiently?


Tag: oracle,sas

The problem I am trying to solve:

I have a SAS dataset work.testData (in the work library) that contains 8 columns and around 1 million rows. All columns are in text (i.e. no numeric data). This SAS dataset is around 100 MB in file size. My objective is to have a step to parse this entire SAS dataset into Oracle. i.e. sort of like a "copy and paste" of the SAS dataset from the SAS platform to the Oracle platform. The rationale behind this is that on a daily basis, this table in Oracle gets "replaced" by the one in SAS which will enable downstream Oracle processes.

My approach to solve the problem:

One-off initial setup in Oracle:

  1. In Oracle, I created a table called testData with a table structure pretty much identical to the SAS dataset testData. (i.e. Same table name, same number of columns, same column names, etc.).

On-going repeating process:

  1. In SAS, do a SQL-pass through to truncate ora.testData (i.e. remove all rows whilst keeping the table structure). This ensure the ora.testData is empty before inserting from SAS.
  2. In SAS, a LIBNAME statement to assign the Oracle database as a SAS library (called ora). So I can "see" what's in Oracle and perform read/update from SAS.
  3. In SAS, a PROC SQL procedure to "insert" data from the SAS dataset work.testData into the Oracle table ora.testData.

Sample codes

One-off initial setup in Oracle:

Step 1: Run this Oracle SQL Script in Oracle SQL Developer (to create table structure for table testData. 0 rows of data to begin with.)

DROP TABLE testData;
    NODENAME          VARCHAR2(64) NOT NULL,
    TS                VARCHAR2(10) NOT NULL,

On-going repeating process:

Step 2, 3 and 4: Run this SAS code in SAS

******* On-going repeatable process starts here ******;

*** Step 2: Trancate the temporary Oracle transaction dataset;
proc sql;
  connect to oracle (user=XXX password=YYY path=ZZZ);
  execute (
    truncate table testData
  ) by oracle;
  execute (
  ) by oracle;
  disconnect from oracle;

*** Step 3: Assign Oracle DB as a libname;
LIBNAME ora Oracle user=XXX password=YYY path=ZZZ dbcommit=100000;

*** Step 4: Insert data from SAS to Oracle;
  insert into ora.testData
  select NODENAME length=64,
         STORAGE_NAME length=100,
         TS length=10,
         STORAGE_TYPE length=12,
         CAPACITY_MB length=11,
         MAX_UTIL_PCT length=12,
         AVG_UTIL_PCT length=12,
         JOBRUN_START_TIME length=19
  from work.testData; 

**** On-going repeatable process ends here       *****;

The limitation / problem to my approach:

The Proc SQL step (that transfer 100 MB of data from SAS to Oracle) takes around 5 hours to perform - the job takes too long to run!

The Question:

Is there a more sensible way to perform data transfer from SAS to Oracle? (i.e. updating an Oracle table from SAS).


First off, you can do the drop/recreate from SAS if that's a necessity. I wouldn't drop and recreate each time - a truncate seems easier to get the same results - but if you have other reasons then that's fine; but either way you can use execute (truncate table xyz) from oracle or similar to drop, using a pass-through connection.

Second, assuming there are no constraints or indexes on the table - which seems likely given you are dropping and recreating it - you may not be able to improve this, because it may be based on network latency. However, there is one area you should look in the connection settings (which you don't provide): how often SAS commits the data.

There are two ways to control this, the DBCOMMMIT setting and the BULKLOAD setting. The former controls how frequently commits are executed (so if DBCOMMIT=100 then a commit is executed every 100 rows). More frequent commits = less data is lost if a random failure occurs, but much slower execution. DBCOMMIT defaults to 0 for PROC SQL INSERT, which means just make one commit (fastest option assuming no errors), so this is less likely to be helpful unless you're overriding this.

Bulkload is probably my recommendation; that uses SQLLDR to load your data, ie, it batches the whole bit over to Oracle and then says 'Load this please, thanks.' It only works with certain settings and certain kinds of queries, but it ought to work here (subject to other conditions - read the documentation page above).

If you're using BULKLOAD, then you may be up against network latency. 5 hours for 100 MB seems slow, but I've seen all sorts of things in my (relatively short) day. If BULKLOAD didn't work I would probably bring in the Oracle DBAs and have them troubleshoot this, starting from a .csv file and a SQL*LDR command file (which should be basically identical to what SAS is doing with BULKLOAD); they should know how to troubleshoot that and at least be able to monitor performance of the database itself. If there are constraints on other tables that are problematic here (ie, other tables that too-frequently recalculate themselves based on your inserts or whatever), they should be able to find out and recommend solutions.

You could look into PROC DBLOAD, which sometimes is faster than inserts in SQL (though all in all shouldn't really be, and is an 'older' procedure not used too much anymore). You could also look into whether you can avoid doing a complete flush and fill (ie, if there's a way to transfer less data across the network), or even simply shrinking the column sizes.


How to use subquery result as the column name of another query

I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example: select item, item_type ... forcast_0 * 0.9 as finalforcast forcast_0 * 0.8 as...

Why I can't compare dates?

I have this simple query: select, h.time from main_data sm INNER JOIN TA t on = AND t.first=1 LEFT OUTER JOIN History h on WHERE trunc(TO_DATE(h.time, 'DD-MM-YYYY')) BETWEEN trunc(TO_DATE('07.05.2015', 'DD.MM.YYYY')) AND trunc(TO_DATE('07.06.2015', 'DD.MM.YYYY')); h.time looks like 07-MAY-15 The above query returns no results, even if...

Calculating overlap between groups

I have a table with two columns of interest, item_id and bucket_id. There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to. Each item_id can appear multiple times, but each occurrence will have a separate bucket_id value. For example, the...

Column ambiguously defined error with Oracle Merge statement

I have a problem with this query in Oracle SQL. Errore alla riga del comando:18 Colonna:42 Report errori: Errore SQL: ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause: *Action: This is the query, but i don't see the column ambiguously defined: MERGE INTO T_HPSM_CM_UBIS H USING (SELECT...

Using MyBatis Update with foreach

i am struggling with update statement in MyBatis. I want to put multiple strings in one row: UPDATE MY_FILTERS SET GROUPS = <foreach item="item" collection="selectedGroups" open="" separator="," close=""> #{item} </foreach>, TEMPLATES = <foreach item="item" collection="selectedTemplates" open="" separator="," close=""> #{item} </foreach> where ID = #{id} I've tried the following alternatives: open...

Notice: Array to string conversion in “path of php file” on line 64

PHP CODE -: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""> <html xmlns=""> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $rows =0; $fp = fopen("leave1.csv","r"); if($fp){ while(!feof($fp)){ $content = fgets($fp); if($content) $rows++; } } fclose($fp); //echo $rows; $_SESSION['rows'] = $rows; ?>...

Need only one match from a Left Join

Hello thanks for taking the time to read this. I have a table TBL_INCIDENT containing columns TXT_INC_ID and TXT_SERVICE, I am using a LEFT JOIN to join it to table TBL_ASMS containing TXT_APPLICATION_ID. The issue I am having is the join will have multiple matches and I only want the...

Why does the date doesn't match with what I have inserted into the database?

I tried to display the sqlcommand after I saved into the database. Everything seemed to work pretty fine, but when I opened my table from TOAD, the dates are wrong. Here is my sql command: INSERT INTO USERTASK (USERTASKKEY, USERID, TASKKEY, TASKDATE, CREATEDATE, CREATEUSERID) VALUES (USERTASKSEQUENCE.NEXTVAL, 'admin2', '1', TO_DATE('05-06-2015','yyyy/mm/dd HH24:MI:SS'),...

Oracle SQL - Returning the count from a delimited field

I'm fairly inexperienced with SQL so hopefully this question is not too silly. Here is the scenario: I have a VARCHAR2 column that stores a series of values delimited by product. Depending on on the account, they can have one or multiple products. I'm trying to write a query that...

Trigger to find next available inventory location

I am trying to implement inventory tracking and am running into problems. As this is my first foray into database triggers (& PL/SQL in general) I think I need an adjustment to my thinking/understanding of how to solve this issue. My situation is as follows: Each time a new item...

Dealing with nulls when selecting in MyBatis

I have a query which at first launch returns zero rows: <select id="getParams" parameterType="map" resultMap="ParamsResultMap"> SELECT ID, GROUPS, TEMPLATES, DATE_FROM, DATE_TO FROM MY_FILTERS WHERE ID = #{id} </select> <resultMap id="ParamsResultMap" type="my.domain.ParamsVO"> <result column="ID" property="id"/> <result column="GROUPS" property="groups"/> <result column="TEMPLATES" property="templates"/> <result column="DATE_FROM" property="dateFrom" jdbcType="TIMESTAMP"/> <result...

how to calculate weighted average but exclude the object itself using SAS

There are four variables in my dataset. Company shows the company's name. Return is the return of Company at day Date. Weight is the weight of this company in the market. I want to keep all variables in the original file, and create an additional variable which is the market...

Get unmatched records without using oracle minus except not in

Actually I have two table and each having column name, I just want the result which are not there in Table2 Table1 ---- Name --- |A| |B| |C| |D| Table2 ------ |Name| ----- |A| |B| Answer |C| |D| I am able to do it by using minus select name from...

How to join 2 tables with select and count in single query

I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the sql. Person: ID NAME AGE...

SQL*Loader Control File Custom Date Format

I need to import from a CSV file in which timestamps are given in the following format 2014-06-14T09:38:29 I tried the following in the control file for SQL*Loader but it doesn't work TIME DATE "YYYY-MM-DDTHH:MI:SS" and TIME DATE "YYYY-MM-DDTHH24:MI:SS" How can I parse this custom date? The error I get...

SQL Developer does not connect with SID as defined in tnsnames.ora


SAS proc sql with when then statements

I have a "time" var of years in my data. I need to create a new var based on the following with PROC SQL if time>mean(time)then new var=1 else, new var=0 I keep getting different error, how can I improve my code? proc sql; create table v3 as select*,case when...

Result from pipelined function, always will sorted as “written”, or not?

Needed get renumbered result set, for example: CREATE OR REPLACE TYPE nums_list IS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(from_n INTEGER, to_n INTEGER, cycle_max INTEGER) RETURN nums_list PIPELINED AS cycle_iteration INTEGER := from_n; BEGIN FOR i IN from_n..to_n LOOP PIPE ROW( cycle_iteration ); cycle_iteration := cycle_iteration + 1; IF...

Entity Framework code-first: querying a view with no primary key

Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?

I would like to catch specific exception for exception "ORA-06535: statement string in OPEN is NULL or 0 length" But couldn't figure out the exact "exception name" for it. Please note I don't want to catch it under generic other exceptions block.. EXCEPTION WHEN <exception_name1> THEN executable_statements; WHEN <exception_nameN> THEN...

calculate market weighted return using SAS

I have four variables Name, Date, MarketCap and Return. Name is the company name. Date is the time stamp. MarketCap shows the size of the company. Return is its return at day Date. I want to create an additional variable MarketReturn which is the value weighted return of the market...

Cant delete in database because of constraints

I am making an project and I am trying to delete a row in my database but that table got constraints. I am using this method to delete it.How can I rewrite it so that all constraints are disabled or something in order that i can properly delete a...

SQL Oracle | How would I select a substring where it begins with a certain letter and ends with a certain symbol?

If I had this: NAME EYES==ID==HAIR Jon Brown==F9182==Red May Blue==F10100==Brown Bill Hazel/Green==F123==Brown ...and I wanted to create a new ID column with the ID alone, and I know that everyone's ID starts with an 'F' and will end at the '=' how would I select a substring from the compact...

Using for loop indices in variable generation SAS

I would like to set up a for loop in SAS where I would like to create time dependent tables. The idea is rather simple. I have multiple tables where I would like to left join them and i would like to this operation for every month. I dont have...

How to pull the date in proper format from timestamp

I am working in Oracle SQL Developer and have the following code SELECT Trunc(assigned_date, 'IW') AS bonus_week FROM (SELECT Trunc(blf.assigned_date) AS assigned_date FROM olap.bonus_lifecycle_fact blf) I get output in this form 07-12-03 08-01-28 08-01-28 08-01-28 08-01-07 Which is DD-MM-YY That's fine, except when I export to Excel I get some...

Regex with whitespaces and preceding zeros

I want to match the string 11 with a regular Expression in SAS. The 11 can be preceded by zero or more 0 and/or by white spaces. Any other character is not allowed. Likewise, if anything there should only be white spaces following the 11. Examples: Match: 0000011 11 11<space><space>...

Fill with zero to complete a defined number in sql [closed]

I need to complete cards numbers in sql. I have the prefix =11111 and the number of the card which is variable, therefore it could be '25' or '2130' but at the end I must have 14 numbers. So I need to fill spaces with zeros. I've read about 'LPAD'...

update a table from another table using oracle db

I have to update one table from another one: I can do the update with the MySQL sgbd: update product pr , provider p set pr.provider_name = where p.provider_id = pr.provider_id ; but when I try to do it with oracle : I tried this query for oracle UPDATE...

SQL Oracle | How to delete records from a table when they match another table?

How would I delete records from a table where they match a delete table? As in, I have a table of record keys that say what need to be deleted from my main table. How would I write a delete to say "delete anything from my main table where this...

sql script to find index's tablespace_name only

Trying to find the specific tablespace names that were created for indexes only. I don't want to see the tablespaces names that are already used for tables. Something like below, but i couldn't have it working due to syntax error. Can someone fix it? select tablespace_name from dba_indexes where tablespace_name...

Groovy - timestamp from minutes

I have an array or times/values coming back to be in an array like: [0, 60] Which are times in minutes, 0 = 12:00 a.m, 60 = 1:00 a.m. I am wanting to store these in an oracle database as timestamps. How do I convert minutes into timestamps in groovy?...

'ORA-00942: table or view does not exist' only when running within a Stored procedure

This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a previous question. If so, please provide a link to the exact logical duplicate question that...

oracle sql error Case When Then Else

SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

Optimizer using an index not present in the current schema

CONNECT alll/all SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id FROM hr.employees WHERE department_id > 50; Execution Plan Plan hash value: 2056577954 | Id | Operation | Name | Rows | Bytes | | 0 | SELECT STATEMENT | | 25 | 200 | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES...

How to derive years of service for employees that have termed and returned several times

I'm working with Oracle and have sample data to show you regarding my question (sample data is below). I'm trying to figure out how to derive years of service for employees that have terminated and returned several times (up to 6 times). The business rules are that if someone leaves...

Can't obtain connection with the DB due to very long schema validation and connection reset afterwards

I have an app where I use Hibernate/Oracle 11g XE/Jboss6.2 and I am having trouble starting up the application on my homolog server at DigitalOcean (Jboss and Oracle xe installed locally). Running everything on my dev machine I have no problem at all and it starts in about 30s, but...

Trying to access Oracle's Maven repository

I'm attempting to access Oracle's repository. Oracle doesn't make it easy. However, I'm attempting to follow the documentation that Oracle provided. I've: Approved the licensing agreement on my system (in case there's some sort of cookie that needs to be set). Set both M2_HOME and MAVEN_HOME to /usr/share/apache-maven. Created an...

Why does .Where() with a Func parameter executes the query?

Here's how my DataAccessLayer works : public Foo GetFooBy(Func<Foo, bool> filter) { var query = from item in this.DataService.FooSet select item; var where = query.Where(filter); var first = where.First(); return first; } I assumed the query would be run when First() is called but it's actually executed by the Where()....

sas macro: argument to be a word in filename [duplicate]

This question already has an answer here: Why won't my macro variable resolve? 1 answer I have similar files in a specific folder. I need to run same program for every files. So I thought of using macro. But I encountered a problem. %macro xyz(cityname); *IMPORTING FILE; proc import...

like and regexp_like

For like we have %. for ex: if we give ad% it ll fetch all the records which starts with ad but i should use regexp_like. for regexp_like what can be used so that it acts as % for like. i cant use ^ad because from UI we ll give...

PLSQL - Error in associative array

Im trying to delete a set of tables and afterwards I want to recreate them using as select from. For couriousity I wanted to do this with an associative array. Unfortunately something is messed up, several errors appear and I can't find the reasons. This is the code: DECLARE TYPE...

Get only Oracle function return table's columns and their types

Is there an oracle query which I can use to get back the details of an oracle function which returns a table, where I'm looking for the info of that table, say the record name, but mostly the columns in that record and their types. Example function: create or replace...

Oracle 11g Insert Statement into Multiple Tables

I am currently having trouble trying to execute multiple statements at the same time. I keep getting this error when trying to run the following INSERT statements: INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE)); INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id,...

Exclude / ignore weekends in Oracle SQL

I made a query that groups text together and their start and end date, i want to exclude/ignore the weekends. In this example 11-12 and 18-19 april are the weekends create table t ( d date, v varchar2(10)); insert into t values (date '2015-04-10', 'ne'); insert into t values (date...

Extracting XML data from CLOB

How can I extract Food ItemID and Food Item Name and Quantity from the data as mentioned below. This is in clob column in plsql. <ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" Currency Id="43"/> </FoodItemDetails> <BillOption> <Bill Details Total Price="22222" BillOption="cash"/> </BillOption> <Authoritativeness/>...

Insufficient authorisation to lst in SAS batch job

Today I faced a problem and solved it, but I am not quite sure why the problem occured. We have a SAS batch job: /path_to_script/ -log /some_path/Logs/replication_#Y.#m.#d_#H.#M.#s.log -batch -noterminal -logparm "rollover=session" -sysin /another_path/macros/ And today the job fell over with error: ERROR: Insufficient authorization to access /sas_path/sasconfig/Lev1/SASApp/replication.lst. I found that...

Identifier is too long

Please help me to find why am getting identifier is too long error , even after having enough space for role in record. Exception : Error report - ORA-06550: line 14, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long ORA-06550: line 18, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long 06550. 00000...

SQL Error: ORA-00933: SQL command not properly ended in Oracle Update query

I have SYSTEM_SQL_CHECK table in which i have saved sql in CHECK_SQL column. This column is Varchar data type. Now i want to update particular sql.I have written below update sql query but it gives an error SQL Error: ORA-00933: SQL command not properly ended. I also tried to query...

Find any character occur more than 4 times

I want to find any character occurs between 4 and 10 times, I used REGEXP_LIKE but it's valid just for one character 'a' , I want to find for all alphabet: SELECT regex_test_name FROM regex_test WHERE REGEXP_LIKE(regex_test_name, 'a{4,10}') ...

Changing Primary Key in Oracle

I'm updating a table that was originally poorly designed. The table currently has a primary key that is the name of the vendor. This serves as a foreign key to many other tables. This has led to issues with the Vendor name initially being entered incorrectly or with typos that...