Don't use Excel to transfer data. Just remove Excel for such task from the list of tools for ever. Using Excel will sooner then one can hope bring other fancy problems, caused by the nice number of "improvements" Excel uses to transform the data to what Excel things is what you may expect.
If you don't have a sas licence, you have to rely on third-party-tools. I recommend searching outside the sas community.
NEVER, EVER use Excel files to transfer data. NEVER.
Plain text files allow you to inspect them with a simple (but proper, like Notepad++) text editor, so you can easily check them for issues. Excel files have a size limit (1M rows), and looking at the data to know what's REALLY in there involves unpacking the zip archive and inspecting the contained XML files.
But you have SAS datasets, so somebody must have a SAS license. Ask them to export the data.
If it was actually your organization which has dropped the SAS license in the meantime, then I must say: using a proprietary format for long-time data storage where it is not sure that the necessary software will be available at some point in the future is not proper IT strategy (and I am being EXTREMELY polite here).
How large are your individual dataset files?
we have a team who can do the conversion to text for us with SAS. however our concern is if the conversion gone wrong, we still have the sas dataset to work on with non SAS software like Python.
I understand using SAS to export to text is the easiest way. However, for convenience and flexibility, we would also want to explore the possiblities with using non sas method to convert sas dataset into text to be stored in Oracle.
If you have a team who have SAS including SAS/ACCESS to ODBC or SAS/ACCESS to Oracle then they can load the data for you directly into Oracle minimising the possibility of any transcription errors.
Hello @HeatherNewton
As I have suggested earlier on using python, it can be used to load data into Oracle directly from sas datasets.
There would be no need for an intermediate text or excel file.
If I were faced with a situation where I had to transfer data from SAS datasets to Oracle (or any other RDBMS) using open-source tools, then Python wood by my first choice..
Pandas read_sas() function has an option for encoding. The applicable encoding can be mentioned.
The high level approach will be to read the data from SAS using python, connect to oracle, create table and write data to oracle table. However an analysis/understanding of the requirements will be needed to formulate a proper strategy.
Some helpful references are given below
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sas.html?highlight=read
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html
https://www.oracle.com/database/technologies/appdev/python/quickstartpythononprem.html
As suggested by @japelin google is a very helpful resource.
As already proposed by @Ksharp quite a bit earlier in this discussion you can use Python (Pandas) to read a SAS file directly into a Pandas dataframe and then export this dataframe to a text file in the format Oracle can read it.
The Oracle guys need to tell you how they would like this text file to look like.
Yes very true
I got error message
'utf-8' codec can't decpde byte 0xc0 in position 0: invalid start byte
what encoding shall I be using ??
also if instead I would love to get text file not excel, how to do it? is CSV same as text?
So did you try FreelanceReinhard suggested SAS Universal View ?
If you have large numbers of files and need to automate, then you can use PowerShell with the SAS Local Provider for OLE DB. This assumes you can run this on Windows.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.