topic Update table in Oracle via SAS in SAS Programming

Update table in Oracle via SAS

Babloo — Tue, 03 May 2016 19:12:50 GMT

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.

Re: Update table in Oracle via SAS

Reeza — Tue, 03 May 2016 19:50:52 GMT

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.

Re: Update table in Oracle via SAS

LinusH — Wed, 04 May 2016 08:57:28 GMT

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.