1 / 46

Session #: Developing SAS Applications using Microsoft .NET

Session #: Developing SAS Applications using Microsoft .NET . The Statistics Canada Perspective. 2009. April 15-16, 2009. Jim Brisbane Systems Development Division. When do we run SAS from .NET?. When we need a customized UI because of:

shae
Download Presentation

Session #: Developing SAS Applications using Microsoft .NET

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session #: Developing SAS Applications using Microsoft .NET The Statistics Canada Perspective 2009 April 15-16, 2009 Jim Brisbane Systems Development Division

  2. When do we run SAS from .NET? • When we need a customized UI because of: • Many input parameters (metadata) for SAS, requiring validation • A need to store SAS output and make it available across a group of users through the UI • A need to add SAS processing to the functionality of an existing .NET application Statistics Canada • Statistique Canada

  3. Why .NET? • In the past the UI was developed using SAS Application Frames (SAS/AF) • Now SAS recommends .NET or Java • Users are on Windows PC’s • Using databases and MS Office • .NET interfaces well with these apps Statistics Canada • Statistique Canada

  4. Three ways to run SAS from .NET 1) Process.Start() 2) SAS’s Integrated Object Model (IOM) 3) As a “custom task” in SAS Enterprise Guide (a .NET application provided by SAS) Statistics Canada • Statistique Canada

  5. Outline of the Presentation • Introduction (done!) • Running SAS using process.start() • Running SAS using SAS’s IOM • Getting XML data from SAS • Real-life applications at Stat Can • Design issues and the Stat Can Application Development Framework for .NET (SADF) Statistics Canada • Statistique Canada

  6. Process.start() To run SAS from the command line: C:\Program Files\SAS\SAS 9.1\sas.exe c:\MySASCode.sas -log c:\MySASCode.log -print c:\MySASCode.lst –nosplash To run SAS from .NET: Process.Start("sas.exe", args) where args = “c:\MySASCode.sas -log c:\MySASCode.log -print c:\MySASCode.lst –nosplash” Statistics Canada • Statistique Canada

  7. Process.start() • String manipulation is easy in .NET • So we read user input from the form, construct the args string, and run process.start Statistics Canada • Statistique Canada

  8. Process.start() • Alternatively, create a Windows script file that contains the command to run SAS • Run the script using process.start() • Convenient if we have a lot of script commands creating Windows environment variables for SAS • Examples: name of the survey to process, network path and name of the input data file, etc Statistics Canada • Statistique Canada

  9. Process.start() Windows script example set InputData = c:\mysaswork\survey23\data\inputs\sample55.sas7bdat set ProcessingOption = 4 set LogFile = c:\mysaswork\survey23\logs\sample55.log set Listing = c:\mysaswork\survey23\listings\sample55.lst set SAS = "C:\Program Files\SAS\SAS 9.1\sas.exe" set SourceCode = c:\mysaswork\source\bigjob.sas %SAS% %SourceCode% -log %LogFile% -print %Listing% -nosplash data in red from the .NET UI Statistics Canada • Statistique Canada

  10. Process.start() SAS code example %let indat = %sysget(InputData); %let option = %sysget(ProcessingOption); • One line of code, executing sysget, gets the value of each Windows variable • 2 variables were passed to SAS, another 2 were used directly in the command to run SAS Statistics Canada • Statistique Canada

  11. Process.start() Script creation from a template set InputData = #SUB1# set ProcessingOption = #SUB2# set LogFile = #SUB3# set Listing = #SUB4# set SAS = "C:\Program Files\SAS\SAS 9.1\sas.exe" set SourceCode = #SUB5# %SAS% %SourceCode% -log %LogFile% -print %Listing% -nosplash Parts previously in red have still to be added Statistics Canada • Statistique Canada

  12. Process.start() 'READ THE TEMPLATE Dim TemplateFileStream as New FileStream(TemplateFileName,FileMode.Open, FileAccess.Read) Dim TemplateStreamReader as New StreamReader(TemplateFileStream) Dim Template as String = TemplateStreamReader.ReadToEnd() 'SUBSTITUTE THE RIGHT PARAMETERS. VALUES WERE READ FROM TEXTBOXES 'OR OTHER CONTROLS INTO STRING VARIABLES Template = Template.Replace("#SUB1#", SASInputFileName) Template = Template.Replace("#SUB2#", Option) < and so on, for SUB3, SUB4, SUB5> 'WRITE THE SCRIPT FILE Dim ScriptFileStream as New FileStream(ScriptFileName, FileMode.OpenOrCreate,FileAccess. Write ) Dim ScriptStreamWriter as New StreamWriter(ScriptFileStream) ScriptStreamWriter.Write(Template) ' EXECUTE THE SCRIPT Process.Start(ScriptFileName) Statistics Canada • Statistique Canada

  13. Process.start() • Template files can be included in the VS project, and in the assembly after deployment • Or store only 1 copy for all users at a permanent network location • Then a change in only 1 place reconfigures the app for all users Statistics Canada • Statistique Canada

  14. Process.start() • After the SAS process is started, it is a separate process • It continues to run after the user shuts down the .NET application • Usually we don’t try to get information back from the SAS program, because it takes a long time to run Statistics Canada • Statistique Canada

  15. Process.start() • Before running SAS, the .NET application can: • Check all required input files exist • Validate the input parameters • Write input parameters to: • XML files which SAS code can read • Windows script variables which SAS code can read Statistics Canada • Statistique Canada

  16. Process.start(): Running SAS on a network server Client PC Server User input from the form Script template file SAS .NET application Launch SAS Final script SAS Grab script Statistics Canada • Statistique Canada

  17. Process.start(): Running SAS on a network server ‘RUN SAS FROM .NET Process.Start("sas.exe", args) ------------------------------ /*SAS CODE CREATES A SAS SESSION ON A SERVER*/ OPTIONS NOXWAIT NOXSYNC; %LET SERVNAME = MYSERVER; OPTIONS REMOTE = SERVNAME; SIGNON SERVNAME USERNAME =_PROMPT; /*THEN RUNS A WINDOWS SCRIPT FILE ON THE SERVER*/ RSUBMIT CONNECTWAIT=NO; OPTIONS NOXWAIT NOXSYNC LS=256; X ""“#SUB NAME OF SCRIPT FILE#"""; ENDRSUBMIT; Statistics Canada • Statistique Canada

  18. Process.start(): Processing a batch list Statistics Canada • Statistique Canada

  19. Process.start(): processing a batch list • When the user clicks “add to batch”, an item is added to the list • When the user clicks “submit batch”, the list is written to disk: \\myserver3\sharename\incomesurvey\project2\data\year3.xml \\myserver3\sharename\incomesurvey\project4\data\group7.xml \\myserver3\sharename\taxsurvey\project5\data\tax4.xml • then the SAS process is started • here, each list item is a file of XML metadata about the SAS job • the metadata includes the network paths and filenames for survey data to be processed • The SAS code contains all the logic to process each item Statistics Canada • Statistique Canada

  20. Process.start(): using XML • Convenient to write an XML file if there are a large number of input parameters to be specified • If you have the parameters in a .NET datatable object, you can write them out with 1 line of code Me.SomeDataSet.MyDotNetTable.WriteXML(StrFileName) • All metadata from MyDotNetTable is written to an XML file with path and filename stored in StrFileName Statistics Canada • Statistique Canada

  21. Process.start(): using XML • The XML data contains a table name tag containing the name of the .NET datatable object it came from <?xml version="1.0" standalone="yes"?> <ISDTAB_METADATADataSet xmlns="http://tempuri.org/ISDTAB_METADATADataSet.xsd"> <MyDotNetTable> …………………………. ……………………. Statistics Canada • Statistique Canada

  22. Process.start(): using XML SAS Code reading the XML: %let XMLFileName=%sysget(MyInputXML); libname MYXML xml "XMLFileName"; data mydata; set MYXML.MyDotNetTable; /* SAME NAME AS THE .NET DATATABLE*/ run; Statistics Canada • Statistique Canada

  23. SAS IOM Server Client PC Host name, Port number, User id, password SAS object spawner .NET application Create the session SAS objects SAS session • Spawner provides .NET with a TCP connection Statistics Canada • Statistique Canada

  24. SAS IOM • A set of COM DLL’s (come with base SAS) • Data from SAS gets read into .NET objects • Allows the user to browse SAS data files on the form • Provides more interaction with the SAS session C:\Program Files\SAS Institute\Shared Files\Integration Technologies or: C:\Program Files\SAS\Shared Files\Integration Technologies • Available from the SAS web site (“SAS Integration Technologies Client”) Statistics Canada • Statistique Canada

  25. SAS IOM In Visual Studio: • Select “add reference” from the project menu • Dialog box pops up, select the COM tab • If base SAS is installed, the list will include 10 to 20 references provided by SAS • List is alphabetical and their names begin with “SAS” • Two most important ones: • SAS object manager (sasoman.dll) • Its type library, the SAS IOM Type Library (sas.tlb) Statistics Canada • Statistique Canada

  26. SAS IOM Statistics Canada • Statistique Canada

  27. SAS IOM • Type libraries are used to create a “run-time callable wrapper” • Translates between .NET data types and COM data types • If SAS is not installed, the COM dll’s need to be manually added to the Windows registry, before you can see them in Visual Studio Statistics Canada • Statistique Canada

  28. SAS IOM: Points to note: • If the user shuts down the UI while the SAS job is still running, the SAS job is killed • SAS IOM is not a .NET standard, so could increase maintenance cost / effort • SAS IOM provides security when a client is running jobs on a server • And SAS IOM is essential when the client must run jobs on a server which is not running Windows Statistics Canada • Statistique Canada

  29. SAS IOM : The Main Classes • SAS.Workspace • SAS sessions are objects of this class, but can’t be instantiated directly • SASObjectManager.ObjectFactory • contains the CreateObjectByServer method which is used to create SAS.Workspace objects • SASObjectManager.ServerDef • Used to create the server object - contains data about the server on which the SAS session is to be created Statistics Canada • Statistique Canada

  30. SAS IOM : The Main Classes • SAS.LanguageService – this class contains the Submit method, used to submit SAS code for execution • SAS.Workspace.FileService • contains methods for manipulating files through the SAS session • DeleteFile • ListFiles • RenameFile • MakeDirectory Statistics Canada • Statistique Canada

  31. SAS IOM Argument list for CreateObjectByServer • Name of the session (workspace) to be created • Boolean = true if the session is to be created before the next line of .NET code executes • The server object (null if the session is local) • User id (empty string for a local session) • Password (empty string for a local session) Statistics Canada • Statistique Canada

  32. SAS IOM : Example – connecting to SAS on Unix from a .NET client (Labour Statistics Division, Survey of Employment, Payroll and Hours) • Survey data is commonly processed using SAS programs on Unix • There is a shortage of people who can use Unix • The programmer created a class called SASConnection • contains a method called Connect, which takes a server name, userid, and password connector = SASConnection.Connector; connector.Connect("lsdsas", txtUserid.Text, txtPassword.Text); Statistics Canada • Statistique Canada

  33. SAS IOM : Example – connecting to SAS on Unix from a .NET client (C#) private SASObjectManager.ServerDef server = null; public SAS.Workspace clientWorkspace; private SASObjectManager.ObjectFactory clientFactory = new ObjectFactory(); public void Connect(string host, string uid, string pwd) { if (!local) { server = new ServerDef(); server.Port = PORT; server.MachineDNSName = HOST; } clientWorkspace = (SAS.Workspace)clientFactory.CreateObjectByServer( CLIENT, SYNCH, server, uid, pwd); Statistics Canada • Statistique Canada

  34. Using SAS with .NET: Real-life applications at Stat Can • The DTD Framework (Distributive Trades Division) • Uses the IOM and is SADF-compliant • The QRCS Adapter (Distributive Trades Division) • Quarterly Retail Commodity Survey • Uses the IOM and is SADF-compliant • Farm Income and Prices Section Data Integration Project (Agriculture Division) • Using SAS to produce reports • still under development Statistics Canada • Statistique Canada

  35. Using SAS with .NET: Real-life applications at Stat Can • The ISDTAB user interface (Income Statistics Division) • Uses Windows scripts to submit long-running SAS jobs • Metadata describing each job is stored and retrieved from SQL server • The SEPH user interface (Labour Statistics Division) • Uses the IOM to run SAS on Unix • Still under development Statistics Canada • Statistique Canada

  36. Design Issues To process large amounts of data, you need 3 physical tiers, often shown like this: metadata Data (large volumes) Client PC Database server Data processing server (business logic) Statistics Canada • Statistique Canada

  37. Design Issues What solution have I been working on? - The ISDTAB Interface • We used 3 physical tiers with the .NET app only on the client PC • The .NET app writes the metadata to XML files (anywhere on the Windows network) and launches a long-running SAS job on a Windows server Statistics Canada • Statistique Canada

  38. Design Issues The ISDTAB interface: Call up SAS on the server Database server (MS SQL Server, metadata only) Data processing server (business logic implemented in SAS) Statistics Canada • Statistique Canada

  39. SADF Has 5 logical layers (based on the documentation): 1) Presentation (the form) 2) Application (code behind the form) 3) Service (communication between the client and the data processing server) 4) Business (in our case = running SAS on the data processing server) 5) Data access (in our case = metadata downloads and updates against SQL Server) Statistics Canada • Statistique Canada

  40. SADF: Part of the .NET application runs on the processing server: Metadata (small volume, .NET remoting) Metadata (small volume) Client PC running layers 1,2,3 Database server Data processing server running layers 4,5 (Running SAS for the business logic – high volume data processing) Statistics Canada • Statistique Canada

  41. In conclusion: • SAS is a powerful tool for large-scale processing of survey data • A lot of Stat Can’s business logic is implemented in SAS • Complex but repetitive processing can be automated using SAS with a user-friendly .NET interface • We need to learn more about developing SADF-compliant applications for running SAS from .NET • More conclusions about how to use SAS with .NET and SADF will follow Statistics Canada • Statistique Canada

  42. Yves DeGuire Joel Orr Vecdet Mehmet-Ali Luc Bigras Jim Brisbane For more information contact: The SAS Technology Centre, R.H. Coats Building, 14th Floor, Statistics Canada, 100 Tunney's Pasture Driveway, Ottawa ON K1A 0T6 E-mail: Yves.Deguire@StatCan.gc.ca Facsimile: 613-951-0607 Statistics Canada • Statistique Canada

  43. Getting data from SAS using XML Writing an XML file from SAS /*MYXMLFILE IS A WINDOWS ENVIRONMENT VARIABLE CONTAINING THE PATH AND FILENAME - COULD BE PASSED FROM THE .NET APP*/ %let XMLFileName=%sysget(MyXMLFile); libname MYXML xml "XMLFileName"; /*CUSTOMERS IS A DATA FILE IN SAS FORMAT*/ data Customers; input customerID CustomerFirstName $ CustomerLastName $; cards; 1 Bob Smith 2 Joe Brown 3 Phil Jones ; run; data MYXML.Customers; set Customers; run; Statistics Canada • Statistique Canada

  44. Getting data from SAS using XML Contents of the XML file <?xml version="1.0" encoding="windows-1252" ?> <TABLE> <CUSTOMERS> <CustomerID> 1 </CustomerID> <CustomerFirstName> Bob </CustomerFirstName> <CustomerLastName> Smith </CustomerLastName> </CUSTOMERS> <CUSTOMERS> <CustomerID> 2 </CustomerID> <CustomerFirstName> Tom </CustomerFirstName> <CustomerLastName> Brown </CustomerLastName> </CUSTOMERS> <CUSTOMERS> <CustomerID> 3 </CustomerID> <CustomerFirstName> Phil </CustomerFirstName> <CustomerLastName> Jones </CustomerLastName> </CUSTOMERS> </TABLE> Statistics Canada • Statistique Canada

  45. Getting data from SAS using XML Reading the XML data into .NET (VB) ' CREATE A NEW DATATABLE Dim Customers as DataTable = New DataTable("Customers") ' READ THE XML FILE INTO THE DATATABLE Dim MyXMLStream as New System.IO.FileStream(MyXMLFile,System.IO.FileMode.Open) Dim MyXMLReader as New System.Xml.XmlTextReader(MyXMLStream) Customers.ReadXml(MyXMLReader) MyXMLReader.Close() Statistics Canada • Statistique Canada

  46. Getting data from SAS using XML The .NET datatable object: • easy to bind to form controls • allows editing • easy to write back to XML • the .NET equivalent of a database table Statistics Canada • Statistique Canada

More Related