CODEWITHSUNDEEP
rexcelsasconverters
Shashaank Sivakumar
Shashaank Sivakumar

Reputation: 417

How to open SAS files using Excel?

I have a set of SAS data sets and I want to open it using Excel or R. I don't have a SAS software with me so i can't use the export option in it. Is there any converter that converts from SAS7BDAT to excel?

Thanks

Upvotes: 3

Views: 38405

Answers (4)

Winston Lee
Winston Lee

Reputation: 107

Here a quick-and-dirty python five-liner to convert a .xpt file to .csv

import pandas as pd
FILE_PATH = "(directory containing file)"
FILE = "ABC"  # filename itself (without suffix)

# Note: might need to substitute the column name of the index (in quotes) for "None" here

df = pd.read_sas(FILE_PATH + FILE + '.XPT', index=None)

df.to_csv(FILE_PATH + FILE + '.csv')

Hopefully this might help someone

Upvotes: 3

student0495
student0495

Reputation: 181

I came across the same "need" and after some research here and there, I found a nice and easy way with R and the latest version of RStudio (as per 2020 June date - the FREE one). Using it, you can open various formats of files and RStudio generates for you the R script it ran behind. You can use this as a starting point, in order to have the .sas7bdat file opened, and then do the conversion step.

Steps to follow in order to import the file using the RStudio "visual" way: Evironment tab -> Import Dataset -> From SAS...

It will ask you to import the haven library. After the installation you will have a tab with the preview of the data within the file and also the R script ran behind which will look like this:

library(haven)
aux <- read_sas("//PATH_ON_YOUR_MACHINE_TO_FILE/actual_file.sas7bdat", NULL)
View(aux)

Notice the NULL there, it has the purpose of converting empty strings to NULL.

But wait, we also need to convert it to a .csv file in order to have the final job done. For this you simply add below those lines from above the following:

write.csv(aux, "actual_file.csv")

Which will produce within the same folder with the original SAS file, the desired .CSV one. If you want to have ";" as separator instead on "," use write.csv2(aux, "actual_file.csv"). Anyway Strings are enclosed by " " so it should be fine.

Upvotes: 0

in_user
in_user

Reputation: 1958

You could use SAS add in for Microsoft office to open the SAS dataset in Excel. Not sure if it is free though.

As Reese suggested you can use - SAS Universal Viewer , its free!!

Here is the link :-

https://support.sas.com/downloads/browse.htm?fil=&cat=74

Or you can download SAS University Edition, which is also free, it is more than just a viewer, you can write and execute programs in here.

http://www.sas.com/en_us/software/university-edition/download-software.html

Upvotes: 2

Dan
Dan

Reputation: 864

I help develop the Colectica for Excel addin, which opens SAS data files in Excel. No SAS software or ODBC configurations are required. The addin directly reads the SAS file and then inserts the data and metadata into your worksheet.

  • Imports SAS .sas7bdat data and column names
  • Imports SAS .sas7bcat formats and value labels when avalaible

The Excel addin is downloadable from http://www.colectica.com/software/colecticaforexcel

Documentation is available in the user manual.

Upvotes: 6

Related Questions