topic Re: Importing files in SAS and storing them in Oracle as blobs in SAS Data Management

Importing files in SAS and storing them in Oracle as blobs

Proventus — Mon, 29 Oct 2018 15:08:28 GMT

Hi, Complete noob here.

I receive a lot of excel files, not very big, maybe 40 rows and a dozen columns, and I want to store them as blobs in Oracle. Does SAS have the capability to store, then recall the excel files?

This would certainly help me to manage all these files (thousands).

Thanks.

Re: Importing files in SAS and storing them in Oracle as blobs

Kurt_Bremser — Mon, 29 Oct 2018 15:13:01 GMT

It can be done. Use proc import and point the out= option to the Oracle library.

But be aware that importing directly from Excel files involves guessing on the part of SAS, with inconsistent results.

Re: Importing files in SAS and storing them in Oracle as blobs

Proventus — Mon, 29 Oct 2018 15:19:24 GMT

That's great!.

I know about that one. I always QA the data I import because the layout has been known to spontaneously change 😉

Thanks Kurt!

Re: Importing files in SAS and storing them in Oracle as blobs

DBailey — Mon, 29 Oct 2018 16:09:47 GMT

you can definitely store the row/colum data from the excel sheet in oracle as described....but is that what you want to do? It sounded like you want to store the spreadsheet itself as a binary large object in oracle. I think it can be done as well, but not using proc import.

Re: Importing files in SAS and storing them in Oracle as blobs

Proventus — Mon, 29 Oct 2018 16:20:37 GMT

I'll be storing and reporting on the data, but I also need a spot for the physical files. Storing the physical files on a network disk is not a viable solution in this case. I don't want to start building utilities outside of SAS to do the work. That's what they have now, a mish-mash of tools to do one job.

Re: Importing files in SAS and storing them in Oracle as blobs

LinusH — Mon, 29 Oct 2018 20:05:07 GMT

SAS doesn't unfortunately have a BLOB object. Therefore the SAS/ACCESS to Oracle is quite limited in thus sense as well. You could interact with BLOB using a SAS CHAR. But then you need a way to import an Excel into one variable, and frankly I don't know how to do this.

So, it seem more appropriate to use Oracle tools for this. Still, you could control Oracle action using explicit SQL pass through.

Then I wonder you need to store Excel file in Oracle? Perhaps you should us a store more appropriate for the specific data, such as a content or a document server.

Re: Importing files in SAS and storing them in Oracle as blobs

Kurt_Bremser — Tue, 30 Oct 2018 09:59:01 GMT

The largest "object" that SAS can put into datasets (and handle in procedure and data steps) are strings of length 32767.

IMO, if you want to setup a store for Excel files from within SAS, you will have to create a file repository/directory, copy the files there (preferably using XCMD), and save the location in a character variable in a dataset.

Re: Importing files in SAS and storing them in Oracle as blobs

AngusLooney — Wed, 31 Oct 2018 09:22:26 GMT

Sounds like a pretty exotic/extreme use case to store small Excel files IN Oracle as files/blobs.

Why can't you store them in a managed file repository and just track them, i.e. the paths, locations, md5 hash values, file sizes data times etc in a table in Oracle.

What apps/system are subsequently going to access/retrieve the files? (And how?)