Transferring SQL Server data to SAS ??

• Ham

  SSC Veteran

  Points: 262

  More actions

  October 19, 2005 at 12:35 pm

  #67914

  Is there a way to transfer SQL Server data to a .sas file based on the columns (and its associated data) retrieved from a standard select query? (the query could be retrieving data from one table or from multiple tables via table joins)

• alexx

  Old Hand

  Points: 320

  More actions

  October 19, 2005 at 3:38 pm

  #598503

  sql server can export data to excel, access or .txt files via DTS ....

  it works for you ?

• Denis W. Repke

  Mr or Mrs. 500

  Points: 536

  More actions

  October 20, 2005 at 9:39 am

  #598770

  1. Create a system "Data Source" using the "ODBC Data Source Administrator" that points to your SQL Server database (e.g., "MySQLDatabase").

  2. In SAS, use a "libname" statement to connect SAS to your "Data Source":

     libname

  sql odbc datasrc='MySQLDatabase';

  Once you run this "libname" statement, you can refer directly to any table or view in your SQL database directly as if it were a SAS dataset. You don't really need to convert the data to a separate SAS dataset. If you do need or want to create a SAS dataset, then do the following:

  To create a SAS data file from a SQL view named "MySQLView", run the following data statement:

  data MySASData;

    set sql.MySQLView;

  run;

  This data statement will convert your SQL "view" to a SAS data file in your work folder. To convert it to a "physical" disk file, just use "data MyFolder.MySASData;" where "MyFolder" is the folder refered to by another "libname" statement, such as:

      libname MyFolder 'c:\MyData';

  Hope this helps.

  Denis

   

  
  Denis W. Repke

• pcq0125

  SSCrazy

  Points: 2890

  More actions

  April 25, 2007 at 12:37 pm

  #702470

  I was reading this post and I wonder how does this work on DTS if I want the export it an SAS .xpt format. Also how can I establish a a connect SAS connection to SQL through DTS? Any advice? Thanks.