“It’s not you, it’s me.” Breaking Your SAS Co-dependence

1. “It’s not you, it’s me.”Breaking Your SAS Co-dependence Bob Burnham Tuck School of Business robert.a.burnham@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 they’re 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 Google’s 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. 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.

8. Example #1: If – Then • SAS if (x < 5) then do; put "Less than 5."; end; else do; put "More than 5."; end; • VBA If (x < 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("A2:B5").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 Microsoft’s 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 it’s 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.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

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.CreateWorkspaceByServer("", 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 Manager’s Submit and SubmitLines methods. PublicSub 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 Java’s 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@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.