Automating SAS programs with VBA

[Soan]

Hi everyone,

the SAS add-in offers the possibility to write your own code and execute it, directly from Excel.

Is there any way to add, run or modify such a program with VBA?

I know there's a SASStoredProcess object for manipulating Stored processes.

Is there anything similar for SAS Programs?

Thanks for your help.

Soan

[AlanC · Accepted solution]

Yes. Look for any entry around Integration Technologies and VBA or C#. I personally would not use VBA (dead tech but still works). I automate using C# outside of Excel and use Excel as a datasource and then launch SAS code from there. I have loads of blog posts on the use. Feel free to search and look at my blog on these techniques:

http://savian.blogspot.com/

Essentially, you have this (in C#):

        private Workspace CreateWorkspace()
        {
            Workspace sasWorkspace;
            try
            {
                var objFactory = new ObjectFactoryClass();
                var serverDef = new ServerDefClass();

                // ************************************************
                // Configure the SAS server information.
                // ************************************************

                serverDef.BridgeEncryptionAlgorithm = "SASProprietary";
                serverDef.BridgeEncryptionLevel = EncryptionLevels.EncryptUserAndPassword;
                serverDef.MachineDNSName = DnsName;
                serverDef.Port = Port;
                serverDef.Protocol = Protocols.ProtocolBridge;

                // ************************************************
                // Create the connection.
                // ************************************************

                sasWorkspace =
                    (Workspace) objFactory.CreateObjectByServer("BridgeConnection", true, serverDef, UserId, Password);
            }
            catch (Exception ex)
            {
                util.HandleError(MethodBase.GetCurrentMethod().Name, ex);
                return null;
            }
            return sasWorkspace;
        }

        /// <summary>
        ///     Submits SAS code using the IOM interface
        /// </summary>
        /// <param name="sasCode">The SAS statements to be submitted</param>
        public SasRunObject SubmitSasCode(string sasCode)
        {
            try
            {
                Workspace ws = CreateWorkspace();
                Common.StepError = false;
                Common.SasLanguageService.Reset();
                Common.SasLanguageService = ws.LanguageService;
                Common.SasLanguageService.Submit(sasCode);
                Common.SasLanguageService.StepError += SasLanguageService_StepError;
                Common.SasLanguageService.LineSeparator = "\r\n";
                var sro = new SasRunObject();
                sro.SasCode = sasCode;
                sro.SasLog = Common.SasLanguageService.FlushLog(Common.LogCharactersToReturn);
                sro.SasList = Common.SasLanguageService.FlushList(Common.ListCharactersToReturn).Replace("\f", "");
                return sro;
            }
            catch (Exception ex)
            {
                util.HandleError(MethodBase.GetCurrentMethod().Name, ex);
                return null;
            }
        }

Chris Hemendinger has a lot of useful information in his book on Amazon. Highly recommend buying it and using it.

Skip VBA: horrible technology. Loook at ESharper for Excel. Haven't used but was investigating tonight and will probably test.

[Soan]

Thanks Alan,

interesting stuff, I'll be sure to check it out!