Create SAS Data Sets w/o SAS

Scope: 


Create output in SAS7BDAT (closed binary format for passionate SAS practitioners and experts without installing Base SAS product from SAS Programming Institute.

Carolina S-JDBC Driver:


Dulles Research – A Virginia based private company offer “Carolina S-JDBC” JDBC driver that replace the need for Base SAS license.
License constraints: (Carolina S-JDBC Driver Download, 2016) Annual subscription is $995.00 for a single instance of application server. Quote can be requested from enterprise wide installation. Free for non-commercial use.
http://www.dullesresearch.com/sjdbc-download/

Solution:

 class.forName("com.dullesopen.jdbc.Driver").newInstance();
              Connection connection = DriverManager.getConnection(
                      "jdbc:carolina:v2:libnames=
(data1='c:\\gitwork',data2='c:\\temp');");

A S-JDBC license is required to use the driver and SAS7BDAT file can be created very easily using general JDBC SQL Statements.



Program:

Class.forName("com.dullesopen.jdbc.Driver").newInstance();
Connection connection = DriverManager.getConnection(
        "jdbc:carolina:v2:libnames=(data1='c:/gitwork/sas1');");
try {
    Statement statement = connection.createStatement();
    statement.execute("CREATE TABLE data1.event " +
            "(e CHAR(5) label='Event Type', " +
            "n DOUBLE PRECISION, " +
            "d DATE, " +
            "t TIME, " +
            "dt TIMESTAMP)");
    PreparedStatement preparedStatement = connection.prepareStatement(
            "INSERT INTO data1.event (e, n, d, t, dt) " +
                    "VALUES (?, ?, ?, ?, ? )");
    preparedStatement.setString(1, "a");
    preparedStatement.setDouble(2, 10);
    preparedStatement.setDate(3, Date.valueOf("2014-02-28"));
    preparedStatement.setTime(4, Time.valueOf("05:27:43"));
    preparedStatement.setTimestamp(5, Timestamp.valueOf("2014-02-28 14:43:57.678"));
    preparedStatement.execute();
    preparedStatement.setString(1, "b");
    preparedStatement.setDouble(2, 11);
    preparedStatement.setDate(3, null);
    preparedStatement.setNull(4, Types.TIME);
    preparedStatement.setNull(5, Types.TIMESTAMP);
    preparedStatement.execute();
    preparedStatement.close();
    connection.commit();
} finally {
    connection.close();
}

Alternatives:

 ·         Anaconda – Pandas can read SAS7BDAT but not write.

 References:

Carolina S-JDBC Driver Download. (2016, October 6). Retrieved from Dulles Research: http://www.dullesresearch.com/sjdbc-download/


Comments

Post a Comment

Popular posts from this blog

ACID vs BASE

In 1970, Jim Gray defined ACID properties as a reliable means of implementing a transaction system. Atomic – A transaction should be indivisible and it must be all or nothing even when the database goes down due to system failures. Consistency – A transaction should ensure that the data remains valid at all times even in case of application errors and none of the defined rules and constraints on the data must be compromised. Isolation – A transaction must be isolated from other transactions and intermediate results of a transaction must not be seen by other transactions that are concurrently in execution. Durability - A transaction must preserve the state of completed transaction at all times even in the event of system failures. Even though ACID stood the test of time for centralized database system, and we see wide implementation of two-phase commit for distributed databases, scaling databases that are distributed and keeping them highly available has always b...
Read more

Immutable: String

The following are the some of the programmer friendly advantages of high level languages like Java, C# or Visual Basic by handling String as immutable as opposed to being mutable in languages like “C” A) Efficient use of memory: High level language make efficient use of memory by incorporating a special type of memory structure called "String Pool", each time any code creates a string literal, the Java Virtual Machine (Java) or Common language runtime checks to see if the String is already defined in the "StringPool" and if it exists, a reference is assigned as opposed to allocating space for every string. For example: If "ABCDEFGHIJKLMNOPQRSTUVWXYZ" is assigned10 times within a code, then in the StringPool, there will be a single copy of “ABCDEFGHIJKLMNOPQRSTUVWXYZ" and 10 references to it. This is a gain in the memory space allocation even though there is additional over head of maintaining “StringPool” memory structure. In ord...
Read more