Solved

Export SAS results do SQL Server

Posted on 2013-05-15
3
596 Views
Last Modified: 2013-05-22
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!!
0
Comment
Question by:toddinho
  • 2
3 Comments
 
LVL 8

Expert Comment

by:ShannonEE
Comment Utility
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
0
 

Author Comment

by:toddinho
Comment Utility
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!!
0
 
LVL 8

Accepted Solution

by:
ShannonEE earned 500 total points
Comment Utility
Hi there toddinho ,

A bit of background.

SAS has access to "datasets".  They can be organized into "libraries". The easiest set of libraries correspond to all files of type *.sas7bdat in a pc folder (or directory). In a sas job stream you issue something like the following command to make a library called CUSTOMER available.

libname CUSTOMER "c:\project1\Year2012customers";

Open in new window


and then provided the folder c:\project1\Year2012customers exists new datasets can be created there and existing ones can be read.

However, sas  has a large number of engines so that you can set up a library using a non-default engine to access other sources of data.  In your case something like

libname SUPPLY odbc 
     noprompt="DRIVER={SQL Server};SERVER=toddsServer;UID=ToddsUserName;PWD=secret;DATABASE=ToddsDB" ;

Open in new window


or (if your sas system has a licence for the ms sql direct engine

libname SUPPLY sqlsvr
     noprompt="dsn=sqlsvr;UID=ToddsUserName;PWD=secret;" ;

Open in new window


In either case you can change the noprompt into a prompt which will give you an on-screen prompt, and make the data values for the connection available. Use
%put NOTE: connection values are => &SYSDBMSG <=;

Open in new window

to discover what values it uses and then copy those into the noprompt parameter.  The odbc engine or driver is generic and hence is not optimized, whereas the sqlsvr (for SQL Server) would be expected to perform better.

sas files generally have a double barrel name =>  library DOT dataset-name. Example CUSTOMER.Yr2001
All LIBRARY names are restricted to 8 alphanums, but data set names are not restricted except length < 255 chars. If the name has blanks or any special characters which will muck up the syntax then you just put the whole name in quotes FOLLOWED by "N".  For example "data set name with stupid $@&* characters"N   My advice is to make it easy on yourself and keep to leading alpha char, all the rest alphanum and NO blanks.

Any dataset name without a leading library is assumed to be in library WORK.  You may see examples of this is some of the code.  All datasets in the WORK library disappear at the end of the job.

A sas dataset corresponds to a SQL table.

Now you can have sas code like

proc sql;

  create table SUPPLY.newItems as
  select Y1.*,
          Y2.*
  from  CUSTOMER.Year2001 as Y1
  join    CUSTOMER.Year2012 as Y2
  on      (Y1.custNumb = y2.custNumb)
  where (Y1.state = "WA")
  order by Y1.custName;

  create index custNumb(custNumb) on SUPPLY.newItems;

quit;

Open in new window

There are a host of additional parameters to specify on the libname statement to cover special cases if necessary. For example you can specify read only access, how to treat locking, number of rows in a commit, etc, etc ...
see SAS documentation on libname statement for SQL server

Note that in sas code all names (and even keywords) are case insensitive, except for names that are in quotes followed by a "N".  Note here that the N itself is case insensitive.  My suggestion is to see what has been used at your site and keep to that convention.

In common with most SQL implementations you don't need to have the as or the by in order by.  I just like putting them in for ease of understanding.

Regards,

Ian
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now