Running SAS from VBA with full access

Question

Hope someone can help. I am trying to create a routine to run SAS from VBA. I do this with SAS Workspace Manager. As I walk quite far from my comfort zone, I mainly followed what I found in these two sources . Here is 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 a small SAS program, name 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 fine:

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

But whenever I try to execute a SAS procedure that modifies files / libnames, etc., I will get either "Invalid operation for this SAS session" or "User does not have access." Please note that I use SAS locally and not on the server. Therefore, I assume that I am not correctly registered in my SAS work and have not received permission. I thought the userId and password parameters in CreateWorkspaceByServer should be logged in.

So my question will be how to successfully start a SAS session with my credentials on my local computer and get all normal access by opening a window.

To clarify, this SAS process will work fine in a windowed environment:

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

But it will not work with the code "Invalid operation for this SAS session" if it is started with VBA. Something similar happens if I try to write SAS datasets.

I searched for a while, but most of the threads are related to SAS server sessions. Any help would be appreciated.

Answer 1

Well, it seems that most of my problems are related to the fact that I am not the administrator of my system. I was able to write data sets with the above methodology, given that I have write permissions to the directory with my account (duuh). Allas, the x command is stubborn and doesn't work, so I don't get myself an administrator account. So, there are two options to "solve" this problem: 1. Obtain administrator rights. or 2. Forget the workspace manager. Use OleObjects.

Despite the fact that OleObject is less elegant and, possibly, more execution time (not verified), I can use SAS to the full. 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 start a specific process and change some macro variables, as in * ProcessBody; just do OleSAS.Submit ("% let" & variable_name and "=" and "yourValue") and OleSAS.Submit ("% include" and your_program).

In any case, I am very sad to lose the log report report that I had with Worspace Manager, it was very good for quick debugging. Hope this was helpful.

Answer 2

I know that earlier I had problems with SAS / Intrnet that did not have the necessary security policies. I can not guarantee that this will fix, but somewhere to start looking for:

Control Panel-> Administration-> Local Security Policy

Security Settings → Local Policies → User Rights Assignments → Registration as a Batch Job

Make sure that the account that SAS will use to run the tasks has this right.

Also, you say your test.sas program is failing because it is trying to write a data set?

In addition, you may encounter some of the same problems that I had here:

Using SAS and mkdir to create a directory structure in windows