Options for Onedrive integration with SAS Studio/EG

[snoopy369]

My organization has started the move to Onedrive/MS 365, and we're trying to figure out the best way to utilize SAS with files stored in Onedrive or in Teams shared storage.  

Prior to Onedrive, we did have Sharepoint as a location people could store files, and we didn't get requests to "open them in SAS" because nobody had a share mapped to a local folder (possible to do, of course, using WebDAV, but not common here).  However, now that we all have our shares mapped to a local (c:\whatever\Onedrive) folder, we've had requests to be able to store non-sensitive data in these spaces but still be able to access them via SAS.

What are our options here?  I have watched and read Chris Hemedinger's article on using the MS Graph API, and that seems like one possibility, but I tend to consider that "high difficulty"; while I certainly can, and will, use this, I suspect most of our users aren't technically savvy enough to implement this, even if I do as much of the work as possible for them - even if we get past the auth token step, the PROC HTTP access to files will probably be a level of difficulty past what they're comfortable with.  

Our users are mostly "base SAS" users, who were converted to SAS Studio (90%) or Enterprise Guide (10%) using a 9.4TS1M7 SAS server a year or so ago.  They're mostly doing data step/basic Stats procs, importing excel files either as actual data or as metadata to drive processing.  Mostly just making SAS datasets, or basic reports (probably an exported dataset or a proc freq/print).  

How do others solve this problem?  Our ideal solution would be to have a network folder mapped to their Onedrive share and/or synced to Onedrive, but that doesn't seem to be possible.  (It might be possible to do it using symlinks, but I don't think that's a good idea.)  I also saw that it's possible to map a network drive using a URL, but it seems like that's pretty hack-y also and might not be possible for us, as it requires individual client tokens (we'd have to know what client token to use in the level_env_usermods.bat that would do the mapping, and I don't know how we'd do that without some sort of unsafe storage).  We strongly prefer supported, and intended, solutions.

Possibilities I have come up with so far:

Solution	Difficulty	Feature
Graph API	High	Full ability to access files with no manual intervention
Copy files from c:\whatever\onedrive to \\network\share	Low	Entirely manual
SAS Office Add-In	Medium	Can do some things, but we don't use the cooler features, and probably more work to do the basic things

What else is out there?  Anything better?  Or is this just "how it is" for Onedrive, and people shouldn't use the shared storage for anything they want to use with SAS?

[ballardw]

If your users are connecting to a server than all that should be needed to would the common libraries they use to be mapped from the server.

If your users want to also use their own Onedrive you might see if you can have your IT avoid creating the extremely long and full of special characters One drive paths that ours did.

You will likely want to have the users WORK libraries going to a local harddrive so you don't get performance hits for all of the temporary bits SAS uses.

One thing I noticed with our Onedrive disks was that some code for ods statements complained if there was a space after the equal sign in a File= such as Ods rtf file="<path>/file.rtf" ; works but Ods rtf file= "<path>/file.rtf" would have problems.

You may want to provide an automatic macro variable with the base for the users Onedrive path. Ours was over 0 characters long by the time you get the "C:\Users\username\OneDrive - <server>". Typing that out frequently for paths on the account sucks and the space and dash had other potential for typos. If you have a macro then you can do things like

Libname mylib "&onedrivepath\somefolder\otherfolder";

and don't have to type all that stuff.

Maybe your users wouldn't care but I create separate libraries for a lot of ad hoc projects and need the path for libraries, filename statements to read data and ODS destination blocks to write reports.

[snoopy369]

I am confused ... maybe there are some mistaken assumptions on my part here.  What does this mean... " all that should be needed to would the common libraries they use to be mapped from the server."?  For network shares (say, \\network\share\project\subfolder\file.xlsx) then yes, that's what we do now - but they have files in OneDrive or in Teams that aren't on the network shared drives.

When you say the OneDrive path is c:\...\whatever\ is that on the server's c:\ ?  That's what the path is on our local laptop, but the server can't see the laptop of course.  Did your IT set things up so that they (also) have a "synced" onedrive folder on the SAS server?  Can we do that without having them log in interactively?  And I notice that there's two folders on my laptop - one for _my_ onedrive, and one for my "teams" folders - maybe I have a misconfiguration, but we'd need to figure both out, of course...

Thanks!

[ballardw]

Shared libraries should be handled by the server, no references by the individual users to any one drive location or network share as long as the user can access the libraries.

The ONEDRIVE stuff is what each users Onedrive would be. And that is where the server SAS issues arise, the server can't see them. But if users have a stand alone SAS install that is what would be needed. Our organization has stand-alone SAS installs with a limited number of network shares available for a number of reasons many related to who pays for which user's license and they aren't all on the same license because of it.

There are a lot of permutations possible as to SAS use and files location. You should get your SAS admin involved with the IT folks and possibly even contact SAS Tech support for "best practices".

[snoopy369]

Oh, ok, you're talking about stand-alone installs - that's what I'm trying to avoid 🙂 And I'm the SAS Admin, am talking with the IT folks, just hoping some other SAS Admin types might have ideas for how it's done elsewhere.

[SASKiwi]

I think you need to be a bit more specific about your requirements. When you say "files" and SAS Studio/EG what do you mean? Are you referring to SAS program files, EG projects, SAS datasets, SAS input files or SAS output files and reports?

It is also important to differentiate between EG which is a client app installed on your PC and SAS Studio which is an app run in a web browser and it normally runs on your SAS platform mid-tier server. For example, with EG and EG programs and projects any drive your PC can access can be used for storage of these types of files. That would include Onedrive.

The storage locations for other types of SAS files like SAS datasets and input and output files are different. They need to be accessible from the SAS server they get processed on. This would typically be server local or SAN storage. SAS servers have no knowledge of Onedrive. Also I very much doubt that Onedrive would offer good enough IO performance for anything except smallish files.