topic Re: Call VBS within SAS? in SAS Data Science

Call VBS within SAS?

SteveNZ — Thu, 14 Jun 2012 20:10:08 GMT

Hi all,

I have a program that outputs excel files to a mapped drive but not all users of the program remember to map to the drive before running the program.

I've found the following script on the net that automatically maps to the drive so my question is can I package this so it runs directly from SAS?

Ideally I'd like it in a macro that picks up the users username and password. Script that works is:

Dim objNetwork

Set objNetwork = WScript.CreateObject("WScript.Network")

strLocalDrive = "E:"

strUser = "userA"

strPassword = "passwordA"

strRemoteShare = "\\SERVER\etc\etc"

objNetwork.MapNetworkDrive strLocalDrive, strRemoteShare, False

Any help really appreciated.

regards

Steve,

Re: Call VBS within SAS?

data_null__ — Thu, 14 Jun 2012 22:13:48 GMT

Yes you can call that script from within SAS.

However can't you just define a FILEREF using the same syntax \\SERVER\etc\etc.

Re: Call VBS within SAS?

SteveNZ — Thu, 14 Jun 2012 22:33:20 GMT

Would that work? I assumed the drive would have to be mapped. Do you have any sample code that uses username and passwords?

I'd rather use the filenale statement as I've discovered a few hooks in the above script since posting.

Re: Call VBS within SAS?

data_null__ — Thu, 14 Jun 2012 22:50:08 GMT

No I don't have to use passwords. Give it a try and see what happens.

Hopefully someone will come along with more info. There is also the online docs.

Re: Call VBS within SAS?

Tom — Thu, 14 Jun 2012 23:37:09 GMT

First fix the program to write to whatever the user tells it to write to instead of assuming there is E: drive mapped. What do users with 5 or more physical drives on their PCs do? In your example you should be able to replace a path like "E:\myoutput.xls" with "\\SERVER\etc\etc\myoutput.xls".

Otherwise why not just use the net use (net use - Wikipedia, the free encyclopedia) command from the command prompt to map the drive instead of the VBS function call? Is there something special about this drive that requires you to supply username and password? Normally drives should just use the username and password of the user that is trying to connect to them. If that works for you then you can use the system() function %SYSEXEC macro command or a filename with PIPE option to run the net use command from within your SAS program.

Re: Call VBS within SAS?

SteveNZ — Fri, 15 Jun 2012 00:10:49 GMT

Hi @Tom,

Do you have an example I could adapt? I've never needed to look at this area before so am unfamiliar with code required.

Another option I thought of is using the drivemap option to see if the drive is mapped and, if not, sound an alert and not produce the files. I can easily do that but would prefer to get SAS to map the drive for the user.

cheers

Re: Call VBS within SAS?

Tom — Fri, 15 Jun 2012 01:12:24 GMT

This works for me to map my personal network folder to the J: drive letter.

The syntax might be different if you are using a different operating system.

1 data _null_;

2 infile 'net use J: \\mopcitnas05\%username%$' pipe ;

3 input;

4 put _infile_;

5 run;

NOTE: The infile 'net use J: \\mopcitnas05\%username%$' is:

Unnamed Pipe Access Device,

PROCESS=net use J: \\mopcitnas05\%username%$,

RECFM=V,LRECL=256

The command completed successfully.

NOTE: 2 records were read from the infile 'net use J: \\mopcitnas05\%username%$'.

The minimum record length was 0.

The maximum record length was 35.

NOTE: DATA statement used (Total process time):

real time 5.22 seconds

cpu time 0.06 seconds

Re: Call VBS within SAS?

SteveNZ — Fri, 15 Jun 2012 01:39:22 GMT

Hi all,

Tom's idea worked ie:

	data _null_;
	infile 'net use E: \\SERVER\etc\etc /user:username password' pipe ;
	input;
	put _infile_;

run;

Thanks for all the help