topic Re: sas data file in SAS Programming

Access SAS data file without SAS license

HeatherNewton — Tue, 16 Aug 2022 10:59:40 GMT

Hi if I have lots of sas data file but have no sas license, is it possible to convert sas data file using any other softwares and have it saved as text file or excel file etc? or store it in another database like the Oracle?

Re: sas data file

japelin — Thu, 14 Apr 2022 08:08:08 GMT

You can use WPS or CDATA Drivers for SAS Datasets for access SAS datasets.

I don't know if you can access all data sets encrypted, compressed, passworded, etc.

Re: sas data file

andreas_lds — Thu, 14 Apr 2022 08:43:36 GMT

I would not like to rely on third-party-tools that may or may not be able to read datasets. Before moving away from SAS - a strange idea - i would save all dataset to a database, if the required access module is licensed. If not, exporting to json is possible, if you have a recent release of sas. Or, as export to csv along with proc contents of the dataset.

Re: sas data file

HeatherNewton — Thu, 14 Apr 2022 08:43:37 GMT

are these free software?

Re: sas data file

japelin — Thu, 14 Apr 2022 08:57:29 GMT

Please check it yourself.

https://www.worldprogramming.com/try-or-buy/editions/

https://www.cdata.com/jp/drivers/sasdatasets/order/

Trial versions of both are available.

Re: sas data file

Kurt_Bremser — Thu, 14 Apr 2022 09:02:22 GMT

@HeatherNewton wrote:

are these free software?

Both supplied links mention prices, so I would say no.

If it is not for commercial, but educational purposes, you can do the conversion on SAS On Demand for Academics.

Re: sas data file

ballardw — Thu, 14 Apr 2022 10:40:29 GMT

Upload the files to SAS On Demand, unless they are really big and do a conversion there to CSV or other file format, then download the converted files.

Some other programs can use/read SAS data sets. Tableau is one for example. SPSS might, been a long time since I used that program.

There used to be some utility programs that were not free that did such conversions but the ones I am familiar with died about the time of SAS 8 was released.

Re: sas data file

Ksharp — Thu, 14 Apr 2022 11:46:14 GMT

You can read SAS dataset and save it as text or excel by Python .

https://www.marsja.se/how-to-read-sas-files-in-python-with-pandas/

Re: sas data file

FreelanceReinh — Thu, 14 Apr 2022 12:35:26 GMT

I've just verified that the SAS Universal Viewer (I'm using release 1.42) can save SAS datasets as CSV or XML files: There is a "Save as..." item in the "Table" menu. Of course, without third-party automation software this would be a tedious manual process if you have "lots of" SAS datasets. Also I would be skeptical about using this for really large datasets, but 100,000 observations with a few variables are no problem.

See partial example output from sashelp.class below. The use of a comma as the decimal separator (which is disastrous in the CSV file) is certainly due to my German Windows regional settings and could be changed there.

CSV:

"Name","Sex","Age","Height","Weight"
"Alfred","M",14,69,112,5
"Alice","F",13,56,5,84

XML:

<?xml version="1.0" encoding="utf-8"?>
<LIBRARY type="GENERIC" version="9.2">
   <CLASS>
      <Name>Alfred</Name>
      <Sex>M</Sex>
      <Age>14</Age>
      <Height>69</Height>
      <Weight>112,5</Weight>
   </CLASS>
   <CLASS>
      <Name>Alice</Name>
      <Sex>F</Sex>
      <Age>13</Age>
      <Height>56,5</Height>
      <Weight>84</Weight>
   </CLASS>

Re: sas data file

Sajid01 — Thu, 14 Apr 2022 21:06:36 GMT

Hello
Python and R can be used to convert SAS datasets into other formats.
I am giving a sample code as follows. This should work

import pandas as pd ;
df=pd.read_sas(r"C:\Users\yourname\actors.sas7bdat",encoding="utf-8");
# saving the excel
df.to_excel(r'C:\Users\yourname\actors.xlsx');
print('DataFrame is written to Excel File successfully.');

Re: sas data file

HeatherNewton — Wed, 11 May 2022 02:38:09 GMT

does anyone know how I can read sas dataset in Oracle database?

I know I can use Python to read sas dataset into excel.

is there a direct way to convert sas dataset to be stored directly in Oracle database?

Re: sas data file

HeatherNewton — Wed, 11 May 2022 02:48:24 GMT

if my sas file is really really large and excel has a limit of no of rows, what can i do?

would converting to text also possible with python?

actually we would like to store in Oracle

Re: sas data file

andreas_lds — Wed, 11 May 2022 05:12:52 GMT

If you have Access To Oracle licensed than you can use tables in oracle nearly the same way you use sas datasets.

As i already said earlier, exporting to csv is always possible, you should export the result of proc contents, too, so that all metadata of a dataset is available, so that using the csv files is easier.

Re: sas data file

Kurt_Bremser — Wed, 11 May 2022 05:51:16 GMT

Exporting to text is done in SAS with a DATA step, no other tools needed. Have your Oracle people give you the preferred file format. If you need assistance with writing the step, post the format requirements (in a new question), and we can give you a "blueprint" DATA step.

Re: sas data file

HeatherNewton — Wed, 11 May 2022 06:55:17 GMT

thanks all,

the problem is we dont have SAS license, we have 7G of SAS datasets that need to be converted to save in Oracle.

how to convert sas dataset without SAS, I have Oracle license. I can use Python. any other means?

if it is really large, can excel takes it or have to convert to text?

Re: sas data file

andreas_lds — Wed, 11 May 2022 07:04:12 GMT

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.

Re: sas data file

Kurt_Bremser — Wed, 11 May 2022 07:07:39 GMT

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?

Re: sas data file

HeatherNewton — Wed, 11 May 2022 08:14:28 GMT

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.

Re: sas data file

SASKiwi — Wed, 11 May 2022 09:34:52 GMT

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.

Re: sas data file

Patrick — Wed, 11 May 2022 11:25:56 GMT

@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.