DATA Step, Macro, Functions and more

Update table in Oracle via SAS

Super Contributor
Posts: 625

Update table in Oracle via SAS

I came across following questions in interview which I felt stumble to answer. Appreciate if anyone guide me to figure out the answer


a) I've 'n' number of datasets (e.g. 1000) in  SAS library. I've to create a report to see total number of observations in each of the SAS dataset under that library. Destinations can be in any format (e.g. HTML or CSV). How to accomplish this?


b) I've a dataset called 'customer_sas' in SAS. I've a another table in Oracle called 'customer_oracle'. Now I've to update the table ('customer_oracle') in Oracle by customer id via SAS code. e.g. if customer id is availble in SAS dataset 'customer_sas' and not in Oracle table 'customer_oracle' then we need to update the Oracle table 'customer_oracle'. Now I would like to understand how to read SAS table and then update the Oracle table via SAS.


Thanks in advance for any help you offer me.

Super User
Posts: 23,778

Re: Update table in Oracle via SAS

1. SASHELP.VTABLE or Dictionary.table - query table based on libname and nobs holds the number of observations. A fairly trivial task to generate the report afterwards. 



Super User
Posts: 5,890

Re: Update table in Oracle via SAS

For b), there's not enough information to give a detailed answer.

But basically, you can use "any" SAS updata mechanism that supports a transaction table, including data step w update/modify, or SQL update. This via a SAS/ACCESS to Oracle libaname. Another option is to upload the SAs table to Oracle, and write an SQL pass thru to execute the update in Oracle.

Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation