Integrating SAS with .NET

[TalonZA]

I have been struggling for some time to be able to integrate SAS EG into my .NET code with very limited success.

The task is relatively simple, given the location of a .egp file, open and run it through an API endpoint.  The documentation suggests that we reference the SASEGScripting dll as a way to do this.  But honestly, this DLL is embarrassing.  It has references to Windows.Forms and uses MessageBox's to display errors.  Obviously, this cannot be used in an API, or any other code running in a non-interactive mode for that matter.

I have resorted to drastic measures such as decompiling the code and removing any references to Windows.Forms which has landed me with some success, but it doesn't make a good case for maintainability.  But, even with this, there are a few things that just break with no real way of detecting them.  The code returns no errors and it seems like everything is running just fine, but then we noticed that simple tasks such as ingesting files are just not working.  References seem to indicate something about SAS.IOMProvider is not registered on the computer.

I am using the latest version of the SAS EG dll's but it all feels very dirty and I am having to hack my way through the SAS source codes just to get anywhere.  This really can't be the way forward!?

Has anyone had any success with something like this and would you be willing to share your approach?

[Kurt_Bremser]

If you want to automate SAS code, save your whole project as a .sas file on the server, and use server-side scheduling to run it in batch mode.

[AlanC]

As a .NET developer myself, I understand some of the frustration. My repos on github (under savian-net) have some info and Chris Hemendinger's posts on sas.com will help. Chris is a .NET developer as well and often posts around C#/SAS/PowerShell.

What I am missing in your post is what you are attempting to do. Decompiling code to investigate is not the answer. The SAS egp file is merely a zip file with a different extension. Rename it and you can view the structure. There is no XSD for the XML files within it. However, I have figured out a lot of them using simple trial and error. Also, every release of SAS EG produces a different layout. The details of the XML files are not known outside of the EG engine. I do have a dll that parses that file but it is not in the public domain (we can discuss).

You can also produce an EG custom add-in. It depends on what you need, where SAS is located, etc. SAS is a very, very complex beast. A few tips will keep you on a narrow, focused path. 

[TalonZA]

@AlanC, thank you for your reply. To set the scene, I am not a SAS developer.

In our organization, we have a well-established ETL tool.  However, this tool is quite limited in its capabilities when it comes to "external tools".  One thing it can do is call an API endpoint.  On the other hand, we also have a well-established set of SAS processes that do some complex calculations.  These processes are saved as .egp files.

So, as a .NET developer, I have been asked to write an API endpoint, that when called by the ETL tool, will execute one of these .egp files.  What the .egp actually does is a bit of a black box to me.  What I know is that it ingests a bunch of files as a predefined location, then spits out the results in a separate location.  Once the process has run, the ETL tool picks up the resulting files and continues with whatever it needs to do.

Thanks for the info about these egp's just being zip files.  I have extracted one and found that it contains 185 code.sas, 16 xml files and log files.  I suspect this will be very different from one egp to the next.

What would you suggest is the best way forward?  Do I continue trying to work with these .egp files, or should I, as suggested by @Kurt_Bremser , convert them to .sas files and run them that way?  I assume there is a better .NET integration for that?

[AlanC]

Ok, that helps. So, let's explore the possibilities. As a side note, I will be releasing a WPF app on GitHub this week/next that unwinds the egp files for you. It is not open source: you can just use the utility. I can enhance as needed. 

On to SAS/C# integration via an API. The way I have done it is to wrap it in a kestrel based app, install as a Windows Service, and let it always run in the background on a server. Front it with swagger (OpenAPI), and execute SAS behind the scenes. Stack the sas.exe instances in a queue and just pop the queue as a request comes in. Put it back in queue when completed. Seems complex but pretty easy actually (I have some code I can share). SAS has a start up time cost which you want to eliminate by holding it in memory. The SAS instances are very, very light so you can queue up several hundred w/o an issue. YMMV.

For executing EGP files specifically, look at how to execute EG code from the command line or using SAS Integration Technologies. I would leave that to the last, however, and get everything else in place. Get kestrel running, get swagger running, get a demo service that runs a command line of some sort. Integrate SAS at the end.

[AlanC]

For C#, SAS uses a COM component in Integration Technologies to run SAS. Code can be found at my github site (savian-net) or any of the code that Chris Hemendinger has written. Chris is the OG of C#/SAS.

[TalonZA]

Thanks @AlanC, I already have my API setup.  It's the execution of SAS that is the problem which is the reason I'm here.  The rest of the code was easy, but this integration is proving much more difficult than it should be.

I was using this paper written by Kevin Kramer, https://support.sas.com/resources/papers/proceedings/proceedings/forum2007/016-2007.pdf.  But it just doesn't work.  The problem once again as I stated in the opening is that you cannot simply reference SASEGScripting.dll because it has a Windows.Forms dependency and uses MessageBox's making non-interactive operation impossible.  On top of that, assuming you can avoid all errors, you cannot get away with simply referencing that one DLL.  The program has dependencies such as SASIOMCommonInterop.dll, SASOManInterop.dll, LibGitToSharp, and the list goes on.  Just to get the program to run, I had to dump all the DLLs from SAS into my output folder.

I will look into executing SAS EG from the command line, but I am just concerned that I will lose control of it (assuming it is some fire-and-forget).

@SASKiwi , thanks for the response.  I have actually spoken about this with my manager and trying to integrate multiple technologies in a pipeline is really overly complicated and he agrees.  We are just stuck in a place where we have the ETL tool and we have the SAS projects.  There are hundreds or thousands of them and we can't rewrite them, not without a massive cost and risk to the business.  We are not scheduling these processes, they are run on demand.

Honestly, I just expected more from such a large company as SAS and I'm very disappointed.  There should be a SAS-supported NuGet package that can be referenced where all the dependencies are taken care of with documentation on the various usages.

UPDATE:  I have managed (unfortunately using my hacked SASEGScripting.dll) to get the SAS EG to run to completion.  My next problem is that it's just not doing what it should.  It is now unable to move files from the dropped location into the SAS for execution.  The same EG executed manually on the server works just fine.  So I think I have some permissions that need to be taken care of.  Frustratingly, SAS reports successful execution even though the logs show otherwise.

I'm not out of the woods yet though, and this application really stinks of maintainability issues with a ton of debt.

I think, as a community, we need to start a community .NET integration GitHub page where we can publish a much-needed NuGet package.  Else, I need to quit my job and apply at SAS so I can sort this out for them.

[Kurt_Bremser]

When you run SAS code in batch from the commandline on the server, the permissions will be those of the the user logged in, and you can always read the log file you get for debugging.

If you need to execute prepared code on an ad-hoc basis, a BI server provides the stored process server for this, which you can access through HTTP.

[AlanC]

Ok, you are preaching to the choir 😉 I have been working with SAS and .NET since .NET 1.0. There is way too much history to go over here so I will skip that. SAS is a legacy system started on mainframes. It has gone through lots of iterations but has loads of legacy aspects. You need to understand how to work with it.

IOM is how you work with SAS via COM. There are 3 types of ways for working with SAS, 2 of them matter in your case: local and IOM. If you have SAS local, the local provider is your way of interaction. IOM is then more of a licensing issue. See my code examples on how to easily get Local set up but you must have SAS installed locally, not EG. EG is a UI wrapper on SAS. It can talk locally or use IOM to communicate with a server. 

IMO, you don't need to unwind EG for dlls. If the UI portion is a hindrance, you can use UiPath and RPA your way out of things. RPA will do the clickity-click for you. 

For 20 years, I have hacked my way through the painful learning that it takes to get SAS to work with C#. When I have the time, I share it on github. Not everything is there but I try and put what I can there when time permits. Feel free to contact me for missing info.

[SASKiwi]

@TalonZA - IMHO your ETL architecture to call SAS seems overly complicated and prone to reliability problems since EG projects can only be run on PCs.

Are you scheduling these jobs by any chance? Server-side scheduling and regular processing (as mentioned by @Kurt_Bremser ) is far more reliable, robust and simpler and doesn't require .NET interfaces.

[AlanC]

A lot of times, SAS processes are on demand. An API provides a way to pass parameters into the program as the parms are received. SAS EG is a series of Tasks chained together. Most of the time they are SAS steps, but they can be .NET steps as well (custom/built-in tasks).

In general, SasKiwi, I agree with you. If the process is an adhoc process, however, you can call it (even on a PC), with a REST API and execute the flow. Alternatively, you can just extract the flow out and execute it as a series of steps. 

Normally, when I put REST APIs on SAS, it is not for EG but standalone SAS programs. Both adhoc and scheduled. Same principles should apply here. There are loads of reasons to front SAS with a .NET API. EG? Not sure but I can understand it.

[TalonZA]

Correct me if I'm wrong, but SAS EG is simply a system that allows a pipeline of SAS programs to run as a single unit.  It's not a stand-alone program.  It would make so much sense if SAS would open-source the code for this.  They would not be giving any insight into the actual internal workings of SAS or anything they need to be concerned about, but open-sourcing this project would allow people like me and you to contribute to it.  For instance, we could then expose better integration points that are much more universal in their usage and make them much more usable.

@AlanC , I tried to fund your username on GitHub and was not successful in finding any code examples.  Do you perhaps have a link?

[TalonZA]

Not "stand alone" in the sense that you still need SAS to be able to execute the programs within it.

[AlanC]

SAS won't open source the code: not going to happen.

Github link:

savian-net (ALAN CHURCHILL) (github.com)

[TalonZA]

So, for anyone dropping into this conversation.  I made some additional changes.

Honestly, I just couldn't get SAS EG and my application to work nicely together.  I just don't think that SAS EG has a strong enough integration model and I was wasting countless hours trying to make it work.  @AlanC was very helpful and seems to have achieved this, if you are really needing to go that route, I think you can reach out to him.

The way I ended up getting this to work pretty flawlessly, is to execute the process in PowerShell via my .NET code.  This decoupled the need for me to import any SAS modules or libraries into my code.  But I have a pretty simple need in that I just need to execute an existing .egp program at some location.

Thanks to everyone who assisted, perhaps I will revisit this in the future when I have more patience or when SAS exposes some better functionality.