Transcript I`m Simply Too Lazy to Quit Excel

“It’s not you, it’s me.”
Breaking Your SAS Co-dependence
Bob Burnham
Tuck School of Business
[email protected]
Pulling in Both Directions
• SAS Integration Technologies – accessing SAS
from VBA and .Net
• SAS Java Object – extending SAS with Java
Why?
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
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
they’re programming in Java, C++, C#,
VisualBasic.Net, Delphi or other languages.
SAS Information Technologies Site
Who wants to use SAS with Excel?
• There are 14,100 postings in Google’s archive of comp.softsys.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.)
Excel’s 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.
Example #1: If – Then
• SAS
• VBA
if (x < 5) then do;
put "Less than 5.";
end;
else do;
put "More than 5.";
end;
If (x < 5) Then
Debug.Print "Less than 5."
Else
Debug.Print "More than 5."
End If
Example #2: Looping
• SAS
• VBA
do x = 1 to 10;
y = x * 2;
put x y;
end;
For x = 1 To 10
y = x * 2
Debug.Print x; y
Next
Learning VBA
• The Macro Recorder
Sub CopyRange()
'
' CopyRange Macro
' Macro recorded 9/27/2003
'
Range("A2:B5").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
End Sub
• Books
“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 Microsoft’s standard for
getting software components to communicate with each
other.
“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
“Hello SAS”: Workspace Manager
• The SASWorkspaceManager allows you to create a SAS
Workspace.
• Each SAS Workspace has it’s own WORK library.
• You may have more than one SAS Workspace object at a time.
“Hello SAS”: Inserting a Module
• Click on the Insert menu and
choose Module.
“Hello SAS”: Adding some code.
Public Sub HelloSAS()
Dim workManager As New SASWorkspaceManager.workspaceManager
Dim sasWorkspace As SAS.workspace
Dim xmlInfo$, code$
Set sasWorkspace = _
workManager.Workspaces.CreateWorkspaceByServer("", _
VisibilityNone, Nothing, "", "", xmlInfo$)
code$ = "data hello; msg='Hello from SAS!'; run;"
sasWorkspace.LanguageService.Submit code$
MsgBox sasWorkspace.LanguageService.FlushLog(10000)
End Sub
code taken directly
from SAS web site
“Hello SAS”: Running It
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.CreateWorkspaceByServer("",
visible, null,"","",out xmlInfo);
langService = sasWorkspace.LanguageService;
langService.Async = false;
}
Submitting SAS Code
• The easiest way to run code is to call the Workspace Manager’s 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
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).
Demo: SAS Connection
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)
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.
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;
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);
SAS Types  Java Types
• SAS has two data types: numeric and
character
• Java has MANY data types.
• SAS’ numeric type is equivalent to Java’s
double.
• SAS’ character type is a Java String.
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 [email protected]
200 Ok.
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)
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;
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.