asked on

Export SAS results do SQL Server

Hello,

I don't know anything about SAS, never used it before. But I can handle myself with SQL Server. In my area I need to use some data (that comes from another area via SAS) to make reports.

The SAS routine does a lot of filters and groups and then exports it to a .xlsx. I use this .xlsx to create the reports.

I want to know if it's possible to send the WHOLE data, without filters or anything, to my SQL Server inside SAS routine... That way I could make data transformations and analysis in a much simpler way...

Thanks for you help!!

Ian

Hi there toddinho ,

Yes it is, the only problem is to choose a method!

You can run a job that will export data to a file (like what is done currently) in many different formats. For your purposes either csv or XML may be the best file formats.

You can run a sas job to export the data directly into a table in another database system, in your case that would be SQLserver. It can either create the table and populate it; or append records; or replace records. What ever you desire! (About the SQL server, I'm sorry for you that you are reduced to using ms crap products).

You can set up a running SAS application that will serve up the data via an ODBC connection whenever it is requested.

you can set up a SAS application to serve up the data in html format and make it available via the web (eg internal intranet)

Choose your method based on the results and provide some information about your current SAS environment.

Note: SAS has a proc SQL which allow you to build and query SAS data sets using SQL syntax. Note there is one particular helpful shortcut that SAS makes available. If you want to do it this way, you can create a table (~ SAS data set) straight out of a query as in

create table example as
select  customerNumber,
           customerName,
           address,
       <etc etc >
from   mainTable
where (customerNumber < 1000000)
order by customerName;

Open in new window

hence the last option is to

Do all your work in SAS. While some of the more advances and complicated SQL syntax is not implemented, those type of tasks can be easily done using other parts of the SAS language.

Regards,
Ian

toddinho

ASKER

Wow, very nice information!!! I'm going to analyze which is the best suit for me... Since I dont have yet access to the files servers in SAS (thanks to the bank bureaucracy), can you send me some example of the second option you presented (creating the table and populating it in SQL Server)??

Thanks for the very useful answer!!

ASKER CERTIFIED SOLUTION

Ian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial