[DOTNET] Execute a SAS File and retrieve results

[edsonbassani]

Hello everybody,

Please, I need to make an integration from SAS into a Dotnet WebApplication where I need to exec a .sas file and then get the results into an object (prefereble a DataSet or DataTable) but I'm stuck at SAS IOM component where I can't find any methods to open this file.

Could somebody help me in how I could exec the .sas file and then how I can get the results, please?

Thank you in advance.

[AlanC]

Look at my libraries on GitHub (savian-net (ALAN CHURCHILL) (github.com)). They should help out. If anything needs more clarity, let me know. Also, Chris Hemendinger has written a lot about it so check his stuff out as well.

Download and install the SAS ODBC and OleDB packages. They should already be installed but just in case, check. You also need the Integration Technologies client. 

Get a DataSet

        public static DataTable GetSasDataSet(string sasLibrary, string dataset)
        {
            OleDbConnection sas = null;
            DataTable dt = new DataTable(dataset);
            try
            {
                sas = new OleDbConnection(@"Provider=SAS.LocalProvider.1; Data Source=" + sasLibrary);
                sas.Open();
                OleDbCommand sasCommand = sas.CreateCommand();
                sasCommand.CommandType = CommandType.TableDirect;
                sasCommand.CommandText = dataset;
                OleDbDataReader sasRead = sasCommand.ExecuteReader();
                dt.Load(sasRead);
                sas.Close();
            }
            catch (Exception ex)
            {
                sas.Close();
                string errMessage = "Unable to get the SAS dataset. Library: " + sasLibrary + ", DataSet: " + dataset + ", " +
                    ex.TargetSite.Name;
                throw ex;
            }
            finally
            {
                sas.Close();
            }
            return dt;
        }

Execute SAS Code

public class SasRunObject
{
    public string SasLog {get;set;}
    public string SasCode {get;set;}
    public string SasList {get;set;}
}
    
internal SasRunObject SubmitSasCode(string reqId, string workarea, string sasCode)
{
    var sro = new SasRunObject();
    try
    {
        StepError = false;
        var timer = new Stopwatch();
        timer.Start();
        var ws = new Workspace();
        SetOptions(ws);
        var ls = ws.LanguageService;
        Log.Info($"Startup time for SAS: {timer.ElapsedMilliseconds} ms");
        timer.Reset();
        //Log.Info($"Submitting SAS code to workspace: {SasLanguageService.}");
        var htmlFile = Path.Combine(workarea, reqId + ".html");

        //Append special code at the top of the SAS to direct output
        sasCode = string.Join(Environment.NewLine, AddRadHeaderCode(reqId, htmlFile), sasCode, AddRadFooterCode());

        ls.Submit(sasCode);
        Log.Info($"Run time of SAS code: {timer.ElapsedMilliseconds} ms");
        timer.Stop();
        ls.StepError += SasLanguageService_StepError;
        ls.LineSeparator = "\r\n";
        sro.ProcessId = ws.GetSasProcessId();
        sro.SasCode = sasCode;
        sro.SasLog = ls.FlushLog(Common.AppConfig.SasConfig.LogCharsToAnalyze);
        sro.SasList = ls.FlushList(Common.AppConfig.SasConfig.ListCharsToAnalyze).Replace("\f", "");
        sro.HtmlOutputFile = htmlFile;
        CloseSasWorkspace(ws);
        return sro;
    }
    catch (Exception ex)
    {
        Log.Error("Error when submitting code", ex);
        return new SasRunObject();
    }
}

private void CloseSasWorkspace(Workspace ws)
{
    //var obWorkspaceManager = new SASWorkspaceManager.WorkspaceManager();
    //obWorkspaceManager.Workspaces.RemoveWorkspaceByUUID(SasWorkSpace.UniqueIdentifier);
    if (ws != null)
    {
        ws.LanguageService.Reset();
        ws.Close();
    }
    ws = null;
}

/// <summary>
/// Sets SAS options for processing
/// </summary>
/// <param name="ws">A SAS workspace to appl options to</param>
private static void SetOptions(SAS.Workspace ws)
{
    Log.Info("Set the SAS system options");
    var opt = ws.Utilities.OptionService;
    var options = new Dictionary<string, string>();

    options.Add("MEMLIB", "4GB");
    options.Add("NOOVP", "");
    options.Add("MEMSIZE", "MAX");
    options.Add("MEMCACHE", "4");
    options.Add("MEMMAXSZ", "12G");

    var sasOpt = options.Select(p => p.Key).ToArray();
    var sasVals = options.Select(p => p.Value).ToArray();
    Array errIndices = new long[options.Count];
    Array errCodes = new string[options.Count];
    Array errMsgs = new string[options.Count];
    try
    {
        opt.SetOptions(sasOpt, sasVals, out errIndices, out errCodes, out errMsgs);
        foreach (var o in options)
        {
            Log.Info($"{o.Key,-15}:{o.Value}");
        }
    }
    catch (Exception ex)
    {
        Log.Error("Error when setting SAS options. Most likely, this is caused by doing an " +
                  "Embed Interop Type on the SAS interop references. Check properties and make " +
                  "sure Embed Interop Type is set to false.", ex);
        if (errMsgs != null)
        {
            foreach (var e in errMsgs)
            {
                Log.Error($"SAS error message when setting options: {e.ToString()}");
            }
        }
        else
        {
            Log.Error($"SAS error message array is empty.");
        }
    }
}

private string AddRadFooterCode()
{
    var sb = new StringBuilder();
    sb.AppendLine($"/*" + new string('=', 50) + "*");
    sb.AppendLine($" | CODE ADDED BY SAVIAN WEB SERVICES ");
    sb.AppendLine(" *" + new string('=', 50) + "*/");
    sb.AppendLine($"ods _all_ close;");
    sb.AppendLine($"*{new string('-', 50)} ;");
    return sb.ToString();
}

private string AddRadHeaderCode(string reqId, string htmlFile)
{
    var sb = new StringBuilder();
    sb.AppendLine($"/*" + new string('=', 50) + "*");
    sb.AppendLine($" | CODE ADDED BY SAVIAN WEB SERVICES ");
    sb.AppendLine(" *" + new string('=', 50) + "*/");
    sb.AppendLine($"options fullstimer;");
    sb.AppendLine();
    sb.AppendLine($"%LET SVCSID = {reqId};");
    sb.AppendLine($"%LET SVCSWORK = {Common.AppConfig.Directories["Requests"]};");
    sb.AppendLine();
    sb.AppendLine($"ods _all_ close;");
    sb.AppendLine($"ods html file='{htmlFile}';");
    sb.AppendLine();
    sb.AppendLine($"*{new string('-', 50)} ;");
    sb.AppendLine();
    return sb.ToString();
}