Automating the import of local Excel files to remote SAS server using Enterprise Guide 8

[fournierd]

On a weekly basis, I would like to import Excel files to a remote SAS server using Enterprise Guide 8. Ideally, all I would need to do is put all the files in a local folder, run a program with no manual intervention to finally have the data on the server.

These Excel files are coming from different providers and have different challenges to solve: hidden sheets or sheets where the name changes (timestamped-ish).

If I use a task, I cannot seem to access the hidden sheets without manual intervention first. The sheets with the changing names will create an error on subsequent runs as the name has changed from the initial configuration. If I use PROC IMPORT, I need to upload the file to the server first and this process doesn't seem to be possible within a program.

I'm my mind, I was going to loop an array containing local files names and pass them to PROC IMPORT.

Is this possible?

[Kurt_Bremser · Accepted solution]

You are fixing the wrong issue. After all the importing is done, you still have to work through every sheet manually to see what's in there and into which part of the SAS process the data should fit.

The import task in EG expects a static file name, so it can't be automated. Since the SAS session itself does not have access to your desktop, it can't read the filenames, and on top of it, it could not instruct EG to send files dynamically.

You can copy the files directly to the SAS server, using a tool like WinSCP, and let the SAS session scan the directory and create the code dynamically.

But given the enormous pile of excrements you get (I read that from your description of this stinking mess), your primary option is to set up proper agreements about file and sheet names, and data structures within the sheets. Otherwise you'll just work yourself into a state of lunacy or burnout.

[fournierd]

That's for confirming what I thought. For the record, I want to avoid lunacy and burnout 🙂

[AlanC]

Yes, you can do this but it is not as straight-forward as you may want. First of all, not everything in EG is built-in. They specifically allowed EG to be extended with custom tasks. i have built several and Chris H has a number of examples on his EG custom tasks posts. You have to keep in mind that EG custom tasks are written in C#, not SAS (do not use the VB.NET examples). That said, C# provides all of the interfaces to SAS. So, now the general solution using a .NET custom task:

1. Create an EG custom task (see Chris Hemendinger's articles on it)

2. Use EPPlus for Excel work (free for most use).

3. Use .NET to transfer the files to your machine/server as needed. This is trivial

4. Use EPPlus code to examine the files and make decisions on where they land, folder-wise

5. Use EG interfaces to server to instruct it to execute the programs

Remember, if EG can do something, you can pretty much do it in a custom task (rule of thumb, not absolute)

Using .NET to submit code, generate SAS code, examine logs, route SAS output is absolutely possible. I do it all of the time. Does it have a learning curve? Sure but then the world of SAS is yours and you aren't restricted in power.

[AlanC]

BTW, what can .NET do with SAS? EG is written in C#.

[ChrisHemedinger]

Not yet mentioned here -- but the Copy Files task in EG can help you get your Excel files into place before PROC IMPORT steps.

To see the Copy Files task in action, watch this 11-minute portion of my SAS Enterprise Guide tutorial.

[Kurt_Bremser]

But it will not be trivial to let EG (or .NET) find an arbitrary number of arbitrarily named files, move them to the server, and then import an arbitrary number of arbitrarily named sheets with arbitrary structure, and finally find out what to do with all this.

What I am pointing at: to automate a task, it must be automatable.

[ChrisHemedinger]

@Kurt_Bremser Agree! At least the Copy Files task can upload a collection of files from a folder, using filename wildcard specs.

Once in a directory that SAS can see, you could use SAS code to iterate and examine each file with LIBNAME XLSX.  Then use that data to form appropriate PROC IMPORT steps.  It's not trivial...but definitely doable. And ultimately more approachable than building your own custom task for EG.  Sometimes that's needed, but I always favor automating as much within SAS as possible for best portability.

[AlanC]

Check on arbitrary number and name, copy, and looping through unknonw number of sheets. All easily done. Without knowing what is being searched for, hard to say how much effort would be involved on each one.:

As a trivial example of the ease:

var files = Directory.GetFiles();

foreach(var file in files)...do..
{ file.CopyTo(....);)

.NET is portable and works on all major O/Ss. The code can also run outside of SAS EG and execute SAS: bonus. 

That said, proc copy is easier for SAS programs but proc import lags far, far behind behind Excel libraries such as Excel interop, EPPlus, GemBox, Aspose, etc. If the goal is simple importation of data, import works fine. If complex logic is needed based upon worksheet contents, you need to be able to work at the cell level (colors, fonts, placement, etc.).

[fournierd]

How would it look like invoking .Net through EG or elsewhere? I'm currently on 8.1.

[AlanC]

Ignore Add-In for now and just do it standalone. Add-in can use this code later. There are millions of .NET developers (and a few that do SAS and C#) so plenty of resources to help.

- Download VS Code of VS Community Edition (both free)
- Download .NET Core 3.1+ SDK
- Add the SAS dlls (reference COM tab in add assemblies, pick the following: SASWorkspaceManager, SASObjectManager, SAS).
- Add nuget reference for EPPlus (handles Excel. See examples everywhere on the web)
- Add other refs as needed (red squiggly lines can be hovered over and MS tool will prompt to add automatically)
- Start writing code. Plenty of examples on how to interface with SAS but here is some to get you started:

using SAS;
....
Workspace ws = GetSasWorkspace();
var lang = ws.LanguageService;
lang.LineSeparator = "\r\n";
lang.ResetLogLineNumbers();
lang.Submit(sasCode);

/*********************************************************************
* IMPORTANT: The following 2 lines MUST be included even though they
* appear to be unused
*********************************************************************/
#pragma warning disable CS0219 // Variable is assigned but its value is never used
const LanguageServiceCarriageControl cc = new LanguageServiceCarriageControl();
const LanguageServiceLineType lt = new LanguageServiceLineType();
#pragma warning restore CS0219 // Variable is assigned but its value is never used

lang.FlushLogLines(100, out Array carriage, out Array lineTypes, out Array lines);
var lineList = lines.OfType<string>().ToList();
var typeList = lineTypes.OfType<SAS.LanguageServiceLineType>().ToList();
lang.Reset();