Read the return value from VBScript into SAS

Question

I have a SAS program which requires many input files whose names vary each month. Rather than update the individual file names in Windows Explorer to hardcoded aliases in SAS or copy and paste the new file names into macro variables, I want SAS to initiate a file prompt where I may select the inputs. I am using SAS 9.4 on Windows 7.

To this end, I have written a VBScript which opens a dialog and returns either the path of a selected file or a blank string if the dialog is canceled or closed.

' GetFilePath.vbs

Option Explicit

Function GetFilePath()

    Dim objExec, strMSHTA, wshShell

    GetFilePath = ""

    strMSHTA = "mshta.exe ""about:<input type=file id=FILE>" _
          & "<script>FILE.click();new ActiveXObject('Scripting.FileSystemObject')" _
          & ".GetStandardStream(1).WriteLine(FILE.value);close();resizeTo(0,0);</script>"""

    Set wshShell = CreateObject( "WScript.Shell" )
    Set objExec = wshShell.Exec( strMSHTA )

    GetFilePath = objExec.StdOut.ReadLine( )

    Set objExec = Nothing
    Set wshShell = Nothing

End Function

'WScript.Echo GetFilePath()

GetFilePath()

Ideally, I would like to assign the return value of GetFilePath.vbs to a macro variable. However, it's not clear to me how to I would do that. The only relevant information I've found deals with reading I/O streams into a data set using an unnamed pipe. I figure that if I can do that, then I could at least use CALL SYMPUT to then assign the return value to a macro variable. However, I cannot get SAS to read in the return value.

An example of what I've been trying is,

filename getfile pipe "C:\temp\GetFilePath.vbs";

data test;
  infile getfile;
  input path $;
run;

The prompt opens, but the return value is not assigned to path.

score 3 · Accepted Answer · edited May 23 '17 at 12:06

3

Just replace the last line of your vbscript file as follows:

WScript.StdOut.Write GetFilePath()

And adjust your sas code like so (no need to declare a filename):

data test;
  infile "cscript.exe C:\temp\GetFilePath.vbs" pipe;
  input path:$100.; /* adjust length as appropriate */ 
run;

The 'trick' was to write the output to STDOUT, rather than echo. In addition, as per this answer, the cscript.exe was necessary (for me, in Windows 2012) when calling the .vbs, to keep the output in the console - rather than bounce it back as a window.

By the way, kudos for a neat approach. I hadn't appreciated how SAS could be used to launch (and read back from) graphical apps in this way!

Here is the minimal working example I used for testing:

' GetFilePath.vbs
Option Explicit
Function GetFilePath()
  GetFilePath  =  InputBox("Provide a value for SAS")
End Function
WScript.StdOut.Write GetFilePath()

On the SAS side:

data _null_;
  infile "cscript.exe C:\Temp\aaa.vbs" pipe;
  input; putlog _infile_;
run;

edited May 23 '17 at 12:06

Community

1
1

answered Oct 27 '16 at 17:58

Allan Bowe

12,306
19
75
124

That's good stuff! If you create a data set, you'll notice that two observations are brought in; the first is a Microsoft disclaimer, the second the file path. It seems like `//NoLogo` is the option to turn off the disclaimer. If I execute `cscript.exe //NoLogo C:\temp\GetFilePath.vbs` in the shell, I indeed get just the file path. However, when I try the same statement in SAS, no observations are written to the data set. Any ideas? – Lorem Ipsum Oct 27 '16 at 18:32
Not sure about the `//NoLogo` option, but to ignore those first lines in SAS just add the following line to your datastep: `if _n_>=4 then output;` (adjust the number as appropriate) – Allan Bowe Oct 27 '16 at 18:40
I found that this worked as well: `data test; length path $ 260. ; infile "cscript.exe C:\temp\GetFilePath.vbs" pipe firstobs = 3; input path $ 260.; run; ` The length of path is 260 because that is the max path length in Windows 7. – Lorem Ipsum Oct 27 '16 at 18:42

Read the return value from VBScript into SAS

1 Answers1