How to convert sas7bdat files to other formats outside SAS session

[lilyevsky]

I run my SAS session on Linux, so it saves the libraries data sets in /tmp as sas7bdat format.

I need to convert these files to some other formats, like csv or Excel, but outside of the SAS session. So basically I need some sort of Linux utility that can do it.

I am using SAS 9.3.

Worst case, Windows utility will do.

[DLing]

*.sas7bitm are SAS item store files used to store various things under SAS's management like registry, ODS graphics, PDF files... etc.  They are not SAS datasets that store data.  If you have SAS datasets, usually one uses SAS to export them to different formats like CSV, Excel.

Why do you not want to use SAS to export things?  I'm not aware of general utilities that convert SAS datasets, though there are other statistics software packages that can import SAS datasets into their environment.

[lilyevsky]

Sorry, I made a mistake in the file type. I meant to say sas7bdat. This is certainly the storage for the dataset.

Here is the problem I am trying to solve. As I mentioned, I control SAS session from a PC. To be more exact, I am running Eslink plugin in Eclipse.

Overall this thing runs fine. I can see my datasets under the "Libraries" node, and I can double-click on it to view.

The problem is, that view is sort of dead picture: you cannot cut and paste, and it is also not persistent - disappears after I restart the tool.

Eslink actually allows to define an external viewer for the data (with the default name "sv.exe" - I don't know what that is).

I managed to figure out what an external viewer would get from Eslink as a parameter, by setting the viewer to a simple batch script. Here is what I saw:

/tmp/SAS_workF9F300006A26_mcm-sas.mooncapital.corp\Curr_holdings.sas7bdat

Obviously this looks like a Linux path, and the file was actually there. Note a funny mix of forward and back slashes (but this can easily be fixed by a wrapper).

So this is what I am thinking: if I had a utility that converts sas7bdat file to svn or xls, I would have a wrapper (set as the viewer) which gets the file from Linux to Windows, converts it (or first converts and then gets it to Windows), and then launches Excel.

I actually tried two things:

1. dsread on Windows

2. Java library named SassyReader - tried on both Windows and Linux

People who suggested those things claimed that they worked for them, but for me they all failed complaining about unknown page type or something like that. I don't know why, can it be because I amusing the latest 9.3? Is it possible that file format changed?

Bottom line, if anybody can tell me how to solve this issue, I would really appreciate it.

[Tom]

You have many choices, and the right one probably depends more on both how you prefer to work and probably some details of what you are doing that I do not understand.

If you are running Windows then you could install the SAS System Viewer 9.1, this is probably the sv.exe that your default settings is looking for.  You can still get it from the SAS download pages.  The last version was 9.1 and it cannot read version 9.2 64bit dataset created on Unix (Sun).  It can read 64bit created on PC and 32bit create on Unix.  I do not know about SAS 9.3 datasets.  This will open individual SAS datasets very nicely.

You could get the "new" SAS Universal Viewer.  This should open all dataset formats, but it does not play as nicely as a browser tool for a single dataset.  It wants to open the whole library and if there are many other datasets there then you can waste a lot of time waiting for it to query each dataset for its metadata.  But perhaps it is more modern and can play nicely with your tools using some type of program to program communication protocol that I am too old to figure out.

You could also just purchase a nice utility like STAT/Transfer that convert SAS datasets into dozens of other formats.

I am a little worried about the path that you are trying to read the file from.   Are you trying to read a SAS dataset from the WORK library of a running SAS process? I do not think that is wise as it might crash SAS or your browser tool or both.  Do you have control over the process that you are running?  Can you have it write these files to another location?  If it is SAS code then just have it write the data out in a different format than a SAS dataset. SAS can write almost any format you want.

I am also a little worried about your SAS work directory being under the /tmp partition.  That could be a recipe to crash your Linux server.  I would recommend that SAS work directories be in a different partition from the /tmp so that you do not run the risk of filling the /tmp directory and bringing your operating system to a halt.

[lilyevsky]

Thanks Tom, you gave me some useful information. We actually did notice that working files in /tmp are taking space, and even worse, they do not go away after the session is closed, they are still there and with the lock file. I guess, there should be some configuration parameter to change the place, and we also should do a cleanup from time to time.

Now let me re-iterate details of how we use it here, so maybe you would have some more advice for us.

The Eslink plugin under Eclipse on Windows PC is configured to connect to the Linux machine (via ssh) and run the session there.

As I said before, it overall runs fine. Somehow they can bring the library files to Eslink and display the data in Eslink internal data browser (which is a pretty dumb browser).

After some experiments with using my own batch script as an "external viewer", I learned that Eslink calls it with one parameter, which is a file path.

If my session was local on PC, the viewer would just open it, but since my session is on Linux, I have to copy it first. This is not a big problem, can be solved with a simple wrapper (of course, I wish Eslink team fixed it, but Eslink is a free software and currently not much is going on there).

By the way, I am not quite sure why you worry about reading data files produced by a running session. This is read-only operation, should not crash anything. At least on Linux, I know for sure, making a copy of a file never causes any problem, even if the file is open by a process.

Anyway, the bigger problem is: after my wrapper script brings the file to PC, I need to either have a nice browser to view it, or, even better, a converter to csv or xls format, so that I can just invoke Excel.

If, instead of converter on Windows, I have one on Linux, it is OK, because I can convert the file on Linux and then copy it to PC.

[Cynthia_sas]

Hi:

  I agree with Tom. I don't know why you are looking for a method to create CSV files -outside- of SAS if you already have SAS running. For example, let's look at the dataset, SASHELP.CLASS -- from within your SAS session or in a batch code submission, you could run the following code to create a CSV file and/or a Spreadsheet Markup Language XML file (Office 2003 specification XML) from the dataset SASHELP.CLASS (CLASS.SAS7BDAT stored in the SASHELP library):

filename myoutcsv '/usr/bin/reports/class.csv';

filename myoutxp '/usr/bin/reports/class.xml';

ods csv file=myoutcsv;

ods tagsets.excelxp file=myoutxp style=sasweb;

proc print data=sashelp.class noobs;

  var name age sex height weight;

run;

proc freq data=sashelp.class;

  tables age / nocum;

run;

ods _all_ close;

Then, once your ASCII text files were created on Linux, you could FTP them from the Linux location path specified in the FILENAME statement (probably NOT usr/bin/reports) to a system where you have Excel, so the CSV or XML file can be opened. Note that you must have Office 2003 or higher to open the XML file created by TAGSETS.EXCELXP.

I do note that ESLINK is available on SOURCEFORGE.NET ( http://eslink.sourceforge.net/) as a user-developed plugin. On that site, you will find this Disclaimer:

"Currently all of this is coffee-break programming - literally. So consider it alpha software and do not expect too much from it. It might work for you but it might as well break totally and destroy your work. Be careful!"

A warning like this would cause me some concern in a production environment.

cynthia

[lilyevsky]

Thanks Synthia,

I understand what you are saying. I know how to create Excel file in from SAS table, it takes only 4 lines of code.

But I don't want to write any additional code for every dataset I need to look at. This is not for production, but for development and debugging.

By the way, Eslink is not a production package at all, it is intended for development (no production environment should be under Eclipse anyway).

My requirement is: in Libraries tree, I should be able to right-click on any dataset, launch external viewer, and magically see it in a spreadsheet. Without any need for extra code in the SAS program.

I heard that AppDev studio is also running under Eclipse. Do you know how good it is? Can it run a session on Linux from PC, like Eslink?

Does it have a good external viewer?

[lilyevsky]

Ok, I got it working, with the use of STAT/Transfer software that Tom mentioned. The real Excel pops up in a fraction of a second, really nice.

Obviously I had to set up some scripts on both sides.

STAT/Transfer cost is just $300 per machine, and it can do much more than SAS files. I wonder why SAS does not just include such converter in the basic package.

[FriedEgg]

I know someone who used this utility:

https://github.com/BioStatMatt/sas7bdat

to convert sas tables to other formats through R (http://www.r-project.org/)

AppDev Studio is mainly for developing Applications that use SAS/IT and SCL to run processes in SAS in their background.  If you wanted to go through the substanstial dev path to do such a thing you could accomplish your goals, but it is a pretty lofty scheme to do something so simple to accomplish with SAS itself.

Eslink is a sort of homebrew version of the SAS Enhanced Viewer (which is build on the eclipse platform also, btw...)

My real recommendation would be to follow Cynthia's plan.

Create a batch program, it will take a file and convert it through sas to a given format, then open the converted file.  Then you can change the filetype association for sas7bdat from SAS to your batch program.