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
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:
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.
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:
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.
Thanks Alan,
interesting stuff, I'll be sure to check it out!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!