BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Soan
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
AlanC
Barite | Level 11

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.

https://github.com/savian-net

View solution in original post

2 REPLIES 2
AlanC
Barite | Level 11

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.

https://github.com/savian-net
Soan
Calcite | Level 5

Thanks Alan,

 

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 2 replies
  • 1090 views
  • 0 likes
  • 2 in conversation