topic Re: SAS Formating - how to create from SQL database in SAS Data Management

SAS Formating - how to create from SQL database

SimonSmithBSA — Fri, 11 Mar 2016 14:58:08 GMT

I currently have no SAS installation. I manage a series of SQL databases.

A client has requested that we provide a SAS XD format file once a month for them. They will only accept the data in SAS XD format.

There is no way that SQL can create a file in that format and searching around there seems little option in doing this.

I guess the question is what is the most cost effective way for me to install SAS (in whatever form necessary) that will allow me to import a SQL file and generate a SAS XD file.

I have no other need to use the data within SAS.

Odd question I know, but any thoughts would be great.

Thanks

Re: SAS Formating - how to create from SQL database

RW9 — Fri, 11 Mar 2016 15:27:29 GMT

By SAS XD format, do you mean a SAS transport file = .xpt? Never heard of an CD format. If it is a SAS version 5 transport file, then the file format is open source. I know some authorities still require this file format.

http://www.lexjansen.com/pharmasug/2009/ad/AD08.pdf

As for getting SAS, well you could use the UE version to do this (its limited and can't remember what the limitations are), otherwise you would need a SAS license. Actually creating the files from within SAS is very simple, but there is that initial cost.

I would again question the need for XPT file with them however. In this day and age XPT should be gone, its very old. Most proprietary formats should be long gone. CSV files are better. XML based data are starting to come through heavily as is other open source plain text, cross system file formats. And these two would be the starting point for my discussions.

Re: SAS Formating - how to create from SQL database

Reeza — Fri, 11 Mar 2016 15:38:38 GMT

If you decide you absolutely require SAS, SAS University Edition will not meet the licensing requirement.

You can install a local version of SAS on a desktop, set up a connection to your SQL server using ODBC and then you can export the data or do whatever else you want in SAS.

If you don't have a license, it may be worth contracting out that conversion...SAS licenses are spendy...

Re: SAS Formating - how to create from SQL database

SimonSmithBSA — Fri, 11 Mar 2016 15:48:36 GMT

Thanks for the input - very useful.

I will go back to my contacts and discuss the possibilities of different formats. It is Pharma though so can see them digging their heels in.

Re: SAS Formating - how to create from SQL database

ballardw — Fri, 11 Mar 2016 16:49:59 GMT

A quick search shows that you might investigate a program called StatTransfer at https://www.stattransfer.com/index.html

On a quick view it looks like it will create SAS transport files from a variety of file formats. If you data base isn't directly suported it looks like it will accept ASCII delimited (CSV) files for input.

Re: SAS Formating - how to create from SQL database

RW9 — Fri, 11 Mar 2016 17:39:37 GMT

I am in Pharma, the only time I dig my heels in (and then I really bury them) is when someone tries to give me an Excel file. Not sure where in the process you are dealing with it, but in the Pharma world there is CDISC, an organisation that deals with standards for data transfer, and there are several models availble all of which are plain text XML, and should be industry standards - CDASH for databases, and SDTM also, ADaM for Analysis.

http://www.cdisc.org/

Generally speaking a lot of clinical data is now sent about via XML and the above models, although XPT does hang around.

Re: SAS Formating - how to create from SQL database

SimonSmithBSA — Mon, 14 Mar 2016 09:29:56 GMT

Thanks for the link.

I had googled looking for a converison program but for some reason came up blank.

This does exactly what I need and a very palatable cost 🙂