1
Its not you, its me.Breaking Your SAS
Co-dependence

• Bob Burnham
• Tuck School of Business
• robert.a.burnham_at_dartmouth.edu

2
Pulling in Both Directions

• SAS Integration Technologies accessing SAS from
  VBA and .Net
• SAS Java Object extending SAS with Java

3
Why?
4
In the words of Larry Wall

• But we know from experience that computer
  languages differ not so much in what they make
  possible, but in what they make easy.
• SAS has been taking steps for a while
• SAS Integration Technologies were in SAS 8
• SAS added hash objects and Perl regular
  expressions in SAS 9
• SAS made JavaObj production in SAS 9.2

5
SAS Integration Technologies

• Utilizes open communication protocols for both
  Windows clients and Java clients to give
  developers the ability to use SAS analytical
  power whether theyre programming in Java, C,
  C, VisualBasic.Net, Delphi or other languages.
• SAS Information Technologies Site

6
Who wants to use SAS with Excel?

• There are 14,100 postings in Googles archive of
  comp.soft-sys.sas that reference Excel.
• There are 31 postings that reference the
  SASWorkspaceManager control which allows you to
  run SAS within Excel (about .22).
• (For comparison, there are 32 references to Star
  Trek in the same group of articles.)

7
Excels Macro Language

• Visual Basic for Applications (VBA) is the built
  in macro language for Microsoft Office.
• VBA is one of many implementations of BASIC that
  evolved from the work of Profs. Kemeny and Kurtz
  at Dartmouth in 1964.
• If you know SAS, you will see a lot of BASIC that
  looks very familiar.

8
Example 1 If Then

• SAS
• if (x lt 5) then do
• put "Less than 5."
• end
• else do
• put "More than 5."
• end

• VBA
• If (x lt 5) Then
• Debug.Print "Less than 5."
• Else
• Debug.Print "More than 5."
• End If

9
Example 2 Looping

• SAS
• do x 1 to 10
• y x 2
• put x y
• end

• VBA
• For x 1 To 10
• y x 2
• Debug.Print x y
• Next

10
Learning VBA

• The Macro Recorder
• Sub CopyRange()
• '
• ' CopyRange Macro
• ' Macro recorded 9/27/2003
• '
• Range("A2B5").Select
• Selection.Copy
• Range("A7").Select
• ActiveSheet.Paste
• End Sub

• Books

11
Hello SAS Integrated Object Model

• The Integrated Object Model is a series of COM
  objects that make it easy to use SAS from many
  different languages.
• The Component Object Model is Microsofts
  standard for getting software components to
  communicate with each other.

12
Hello SAS Getting Started

• In Excel, hit alt-F11 to access Visual Basic.
• Click the Tools menu to view References.
• Click on
• SAS Integrated Object Model
• SASWorkspaceManager

13
Hello SAS Workspace Manager

• The SASWorkspaceManager allows you to create a
  SAS Workspace.
• Each SAS Workspace has its own WORK library.
• You may have more than one SAS Workspace object
  at a time.

14
Hello SAS Inserting a Module

• Click on the Insert menu and choose Module.

15
Hello SAS Adding some code.

• Public Sub HelloSAS()
• Dim workManager As New SASWorkspaceManager.wor
  kspaceManager
• Dim sasWorkspace As SAS.workspace
• Dim xmlInfo, code
• Set sasWorkspace _
• workManager.Workspaces.CreateWorkspaceBySe
  rver("", _
• VisibilityNone, Nothing, "", "",
  xmlInfo)
• code "data hello msg'Hello from SAS!'
  run"
• sasWorkspace.LanguageService.Submit code
• MsgBox sasWorkspace.LanguageService.FlushLog(1
  0000)
• End Sub

code taken directly from SAS web site
16
Hello SAS Running It
17
Off on a Tangent C

• The code in C is almost identical
• public SASConnection()
• string xmlInfo
• sasWM new SASWorkspaceManager.WorkspaceManager
  ()
• SASWorkspaceManager.Visibility visible
  SASWorkspaceManager.Visibility.VisibilityNone
• sasWorkspace (SAS.Workspace)sasWM.Workspaces.C
  reateWorkspaceByServer("",
• visible, null,"","",out xmlInfo)
• langService sasWorkspace.LanguageService
• langService.Async false

18
Submitting SAS Code

• The easiest way to run code is to call the
  Workspace Managers Submit and SubmitLines
  methods.
• Public Sub SubmitCode(sasCode As String)
• Dim i, sasText() As String
• sasText Split(sasCode, vbCrLf)
• sasWorkspace.LanguageService.SubmitLines
  sasText
• End Sub

19
Getting Output

• FlushLog(x) FlushLogLines(x) return x
  characters from the SAS log.
• FlushList(x) FlushListLines(x) return output
  from the SAS output window.
• The examples on the SAS website typically use
  something like FlushLog(100000).

20
Demo SAS Connection
21
SAS JavaObj

• JavaObj, provides a mechanism for instantiating
  Java classes, and accessing fields and methods on
  the resultant objects.
• Imagine a Dog class (or object)
• Fields name, breed, age (nouns)
• Methods eat, walk, sleep, shed (verbs)

22
Declaring a JavaObj

• declare javaobj j("java/lang/String)
• declare javaobj declares the Java Object
• the object will be accessed through the variable
  j
• the object will have a type of java.lang.String
• java.lang is a package contains many of the core
  Java objects including classes for many basic
  data types and mathematical operations.
• Java has a HUGE number of available objects that
  can be leveraged.

23
Using a JavaObj Part I

• data hello
• length hello 20
• if (_N_ 1) then do
• declare javaobj j("java/lang/String", "hello
  world!")
• end
• j.callStringMethod("toUpperCase", hello)
• run

24
Using a JavaObj Part II

• Accessing JavaObject Fields (nouns)
• j.setIntField("i", 100)
• j.setDoubleField("d", 3.14159)
• j.setStringField("s", "abc")
• Accessing JavaObject Methods (verbs)
• j.callIntMethod("im", val)
• j.callDoubleMethod("dm", val)
• j.callStringMethod("sm", str)

25
SAS Types ? Java Types

• SAS has two data types numeric and character
• Java has MANY data types.
• SAS numeric type is equivalent to Javas double.
• SAS character type is a Java String.

26
Dartmouth Name Directory

• Macintosh-10 bburnham telnet
  dnd.dartmouth.edu 902
• Trying 129.170.208.9...
• Connected to dnd.dartmouth.edu.
• Escape character is ''.
• 220 DND server here.
• lookup Bob Burnham, email
• 101 1 1
• 110 Robert.A.Burnham_at_tuck.dartmouth.edu
• 200 Ok.

27
Querying the DND Step 1

• Create a Java class, named DNDLookup, to connect
  to the DND and run a query.
• The DNDLookup class has one public method
• public String lookup(String name)

28
Querying the DND Step 2

• data email
• length name 40 email 60
• if (_N_ 1) then do
• declare JavaObj j ('DNDLookup')
• end
• input name 40.
• j.callStringMethod('lookup', name, email)
• datalines
• Bob Burnham
• Jean Luc Picard
• run

29
Conclusions

• SAS has given programmers the tools to leverage
  the power of what other languages do well. The
  goal of this is to make our jobs easier.
• SAS Integration Technologies allow you to
  integrate SAS into applications written in many
  languages including VBA.
• JavaObj is a very promising tool for extending
  SAS and accessing the vast libraries of code
  written in Java.