sas data file

[andreas_lds]

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.

[Kurt_Bremser]

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?

[HeatherNewton]

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. 

[SASKiwi]

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.

[Sajid01]

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.

[HeatherNewton]

Does it work for all data format e.g. Ebcdic

If direct into Oracle no intermediate text file, how to write?

[japelin]

I think you should try it anyway.
@Sajid01 had suggested sample code.

And, google is your friend.
https://www.google.com/search?q=dataframe+to+oracle+python

[Sajid01]

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.

[Patrick]

@HeatherNewton 

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.

[HeatherNewton]

Hi hi , if i want to test for the accuracy or transcription error when using python to convert sas dataset to text, should I be paying more attention to certain dataset that might have special formats...like ebcdic etc??

[Sajid01]

Yes very true

[HeatherNewton]

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?

[Ksharp]

So did you try FreelanceReinhard suggested SAS Universal View ?

[ChrisHemedinger]

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.

• The Local Provider is SAS-provided software that can read SAS data sets. As such, it may be able to read diverse SAS data files more reliably than open-source Python or R packages.
• But....the Local Provider cannot resolve non-standard (user-defined) formats that you might have defined using PROC FORMAT. For this you need a full SAS environment.
• PowerShell with OLE DB can output CSV files and Excel...among others. You could also capture information like data types/lengths -- descriptor information that may be important.