Run SAS from VBA with full access

Question

I hope someone can help. I'm trying to build a subroutine to launch SAS from VBA. I've been doing so with SAS Workspace Manager. As I'm strolling pretty far from my comfort zone I've followed mostly what I found in these two sources. Here's what I came up with so far in VBA:

    Public Sub SubmitSasProg(usrid As String, passid As String, path As String, sasprog As String, varinput As String, Optional logreturn)
    Dim obWsMgr As New SASWorkspaceManager.WorkspaceManager

Dim obSAS As SAS.Workspace
Dim xmlInfo As String

Set obSAS = obWsMgr.Workspaces.CreateWorkspaceByServer("Local", VisibilityProcess, Nothing, usrid, passid, xmlInfo)

Dim obStoredProcessService As SAS.StoredProcessService
Set obStoredProcessService = obSAS.LanguageService.StoredProcessService

obStoredProcessService.Repository = "file:" & path
obStoredProcessService.Execute sasprog, varinput

If IsMissing(logreturn) Then logreturn = 100000
MsgBox obSAS.LanguageService.FlushLog(logreturn)

End Sub

And I have myself a little SAS program, let's call it "Test.sas":

 %let loopTimes=3;
*ProcessBody;
data a;
do x= 1 to &loopTimes;
y=x*x*x;
output;
end;
run;

Now this line will work juste fine:

Call SubmitSasProg("myuserid", "mypassword", "somepath", "Test", "loopTimes=10")

But whenever I try to execute a SAS proceedure that modifies files/libnames etc. I'll get a either a "Invalid operation for this SAS session" or "User does not have access". Please note that I'm using SAS locally, not on a server. So I'm guessing that I'm not correctly logged in with my SAS worksession and lack permission. I thought that the userId and password parameters in CreateWorkspaceByServer are supposed to log me in.

So my question would be how to succesfully start the SAS session with my credentials on my local computer and have all the normal access I'd have by opening the windowed environement.

Just to clarify, this SAS process would work fine in the windowed environement:

   Data _NULL_;
      *x del C:\WINDOWS;
      x mkdir C:\Users\Myname\Desktop\NewFolder;
   run;

But it would fail with an "Invalid operation for this SAS session" code if started from VBA. Something similar happens if I try to write SAS datasets.

I've been looking for some time now but most threads are about SAS server sessions. Any help would be appreciated.

Answer 1

Well, it looks like most of my problems stem from the fact that I'm not an admin on my system. I have managed to write datasets with the above technique given that I have write privileges on the directory with my account (duuh). Allas, the x command is stubborn and won't work lest I get myself an admin account. So, two choices to «solve» this problem: 1. Get administrator privileges. or 2. Forget the workspace manager. Use OleObjects.

While less elegant and perhaps more time consuming upon execution (not tested) OleObject will let me use SAS to its full extent. Here is the VBA code:

Dim OleSAS As Object
Set OleSAS = CreateObject("SAS.Application")
OleSAS.Visible = True
OleSAS.Top = 1
OleSAS.Title = "Automation Server"
OleSAS.Wait = True
OleSAS.Submit(yourSAScode)
OleSAS.Quit
Set OleSAS = Nothing

If you want to run a specific process and change some macro variables as with *ProcessBody; just do OleSAS.Submit("%let "& variable_name & "=" & "yourValue") and OleSAS.Submit("%include" & your_program).

Anyway, I'm pretty sad of loosing the Log report return that I had with the Worspace Manager, it was really great for fast debugging. Hope this was usefull.