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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.