Accessing a Base SAS Data Set

Requirements

Your Java application must meet the following requirements:

Use the standard JDBC data-access calls.
Specify the appropriate JDBC driver class name.
Specify the appropriate host server user ID and host server password.
Specify the subprotocol and subname for the driver that you are using.
Use the librefs connection property in order to assign a SAS library.
Prepend table names with the libref that identifies the SAS library.

What Are SAS Libraries?

SAS organizes tables in libraries. Before you can use a SAS library, you must tell SAS where it is. One way to identify the library is to use a libref, which is a short name (or alias) for the full physical name of the library. In the SAS programming language, you use a LIBNAME statement in order to define the libref. In your Java application, you use the librefs connection property in order to assign the SAS library.

For example, you have a Base SAS data set named suppliers.sas7bdat that is stored in a directory named c:\sasdata on a SAS Workspace Server. The following line of code shows how you set the librefs connection property in order to define mySasLib.

props.setProperty("librefs", "mySasLib 'c:\\sasdata'");

Often, librefs are already defined for SAS servers, which means you do not need to set the librefs property.

You write your Java application using standard SQL. However, you must prepend a libref to the name of the table that you want to access. For example, after you define mySasLib, you can issue the following query in order to access the data in suppliers.sas7bdat.

String queryString = "SELECT * FROM mySasLib.suppliers";

For more information about SAS libraries, librefs, and the LIBNAME statement, see SAS Language Reference: Dictionary.

Note: For information about how to access a Base SAS data set without using a LIBNAME statement, see “SAS Libraries: Tools for Managing Libraries” in SAS Language Reference: Concepts.

Sample Code for Accessing a Base SAS Data Set

In this example, you use the IOM driver to access a Base SAS data set in a library named mySasLib. Here are the scenario details:

Because you are accessing a SAS Workspace Server, you must use the IOM driver. The driver class name for the IOM driver is com.sas.rio.MVADriver.
Because the workspace server is password-protected, you specify values for the user and password connection properties.
The librefs connection property is used to assign the SAS library located in c:\sasdata.
The host name for the workspace server is c123.na.abc.com, and the port number is 8591.

Tip

To access the same data set on a SAS/SHARE server, use the SAS/SHARE driver and modify the code to use com.sas.net.sharenet.ShareNetDriver as the driver class name. Set sharenet as the URL subprotocol name, and the correct port number for the server.

import java.sql.*;
import java.util.Properties;

public class accessSASData
{

  public static void main(String argv[])
  {
    Connection connection;
    Properties props;
    int i;
    Statement statement;
    String queryString = "SELECT sup_id, sup_name " +
        "FROM mySasLib.suppliers ORDER BY sup_name"; 
    ResultSet result;
    double id;
    String name;
 
    try {
      //CONNECT TO THE SERVER BY USING A CONNECTION PROPERTY LIST
      Class.forName("com.sas.rio.MVADriver");
      props = new Properties();
      props.setProperty("user","jdoe");  
      props.setProperty("password", "4ht8d");  
      props.setProperty("librefs", "mySasLib c:\\sasdata';");

	  connection = DriverManager.getConnection(
          "jdbc:sasiom://c123.na.abc.com:8591", props);
 
      //ACCESS DATA
      statement = connection.createStatement();
      result = statement.executeQuery(queryString);
      while (result.next()){
        id = result.getDouble(1);
        name = result.getString(2);
        System.out.println(id + " " + name);
      }
      statement.close();
      connection.close();
    }
    catch(Exception e){
      System.out.println("error " + e);
    }
  }
}

You can also use the JDBC URL to specify connection properties as shown in the following example:

connection = DriverManager.getConnection(
     "jdbc:sasiom://c123.na.abc.com:8591?user=jdoe&password=4ht8d" + 
     "&librefs=MySasLib 'c:\\sasdata'");

Tip

Separate multiple libraries with a semicolon and enclose the paths within parentheses, as shown here:

properties.setProperty("librefs", 
    "(mySasLib 'c:\\libraryFolder';mySasLib2 'c:\\libraryFolder2')");