BookmarkSubscribeRSS Feed
alepage
Barite | Level 11

Hello,

 

I am trying to start a VB script from a SAS program in EG 5.1 and it never start by itself.

I have to go on windows explorer, select the folder where the vb script is copied and double click on it.

 

Here's the code I have found on the communities

 

* Run the generated program ;
data _null_;
  infile "cscript ""&dir\temp.vbs""" pipe;
  input;
  put _infile_;
run;

 

Does anyone could help me with that issue?

Regards,

Alain

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Could be various things, none of which are debuggable from what you have posted.

First, remove the macro variable and put a path in its place, then copy the string to your command window and see if the command actually runs, i.e. your path could be wrong.

Also do you have command line access from SAS, in a lot of setups this is deactivated for security reasons.

Thirdly, what does temp.vbs actually do?  What information is in the command window that you want to pipe back?  

%sysexec cscript "a:\abc\temp.vbs";

Would seem the simplest way to just execute a VBA script?

alepage
Barite | Level 11

Hello,

 

I have found the following code on the communties web site in order to transform xls Excel file into xlsx file.

 

/****************************************/

/* Create sample files to convert. */

/****************************************/

%let dir=\\Spsas002\dfs\DATA08\SEAR\ANAQUANT\Portrait\BTar2018\V1_Dépot\SORTIES\MS_CT_UNI\Nouveau dossier;

%let ext=xls ;

* Generate Visual Basic program ;

data _null_;

file "'&dir\temp.vbs'";

put "set xlapp = CreateObject(""Excel.Application"")";

put "set fso = CreateObject(""scripting.filesystemobject"")";

put "set myfolder = fso.GetFolder(""&dir"")";

put "set myfiles = myfolder.Files";

put "xlapp.DisplayAlerts = False";

put "for each f in myfiles";

put " ExtName = fso.GetExtensionName(f)";

put " Filename= fso.GetBaseName(f)";

put " If ExtName=""&ext"" then";

put " set mybook = xlapp.Workbooks.Open(f.Path)";

put " xlapp.Visible = false";

put " mybook.SaveAs ""&dir.\"" & Filename & "".xlsx"", 51";

put " mybook.Close";

put " End If";

put "Next";

put "xlapp.DisplayAlerts = True";

put "xlapp.Quit";

put "Set xlapp = Nothing";

put "strScript = Wscript.ScriptFullName";

put "FSO.DeleteFile(strScript)";

run;

 

* Show the generated program ;

data _null_;

infile "'&dir\temp.vbs'";

input;

put _n_ 4. +1 _infile_;

run;

* Run the generated program ;

data _null_;

infile "cscript ""&dir\temp.vbs""" pipe;

input;

put _infile_;

run;

 

The script never start by itself.  I have to got to &dir folder and double click on the script icon.

 

More over, I have try your solution and it does not work.

Regards,

Alain

Kurt_Bremser
Super User

What is the log output from your last data step that runs the cscript? If there's none, the script should have run OK.

Are you sure that the SAS workspace server runs on the same machine that you are looking at for the results of the script?

I suspect that you run the script on the SAS server while expecting that it runs on your desktop.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

So, several things.

First, why use SAS to run VB to change XLS to XLSX?  Seems like a lot of faff.  Add a loop into the VBS and just run that (i.e. drop the SAS part) or just use VB in Excel to do this by opening and saving each file - simple and you only need one tech then.

Second, why the change from XLS to XLSX?  I know XLSX is the newer format, but if your converting datasheets, move them to something portable and readbale by everything (removes the need to do this in the future) something like CSV/XML

 

I am afraid "and it does not work" does not really relate anything to me, its likely that you don't have system access from SAS.

ChrisBrooks
Ammonite | Level 13

The ability to shell out to the operating system and run system commands (such as exexcuting the type of script you describe) is often disabled by system administrators. If you run the following pirece of code

 

proc options option=noxcmd define value;
run;

You should get something like this in your log file telling you the setting for that option

 

 

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 61         
 62         proc options option=noxcmd define value;
 63         run;
 
     SAS (r) Proprietary Software Release 9.4  TS1M4
 
 Option Value Information For SAS Option XCMD
     Value: NOXCMD
     Scope: SAS Session
     How option value set: SAS Session Startup Command Line
 
 
 Option Definition Information for SAS Option XCMD
     Group= ENVDISPLAY
     Group Description: Windows and data display information
     Description: Disables the X command in SAS.
     Type: The option value is of type BOOLEAN
     When Can Set: Session startup (command line or config) only
     Restricted: Your Site Administrator can restrict modification of this option
     Optsave: PROC Optsave or command Dmoptsave will not save this option
 NOTE: PROCEDURE OPTIONS used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

If it is disabled (as in this example) you may have to consult your SAS Administrator to ask for it to be changed.

alepage
Barite | Level 11

Here's the code you have suggeted to use:

 

proc options option=noxcmd define value;

run;

 

You will find below the log file:

 

1 %_eg_hidenotesandsource;

5 %_eg_hidenotesandsource;

22

23 proc options option=noxcmd define value;

24 run;

SAS (r) Proprietary Software Release 9.3 TS1M2

Option Value Information For SAS Option XCMD

Value: XCMD

Scope: SAS Session

How option value set: Shipped Default

 

Option Definition Information for SAS Option XCMD

Group= ENVDISPLAY

Group Description: Windows and data display information

Description: The X Command is valid in this SAS session

Type: The option value is of type BOOLEAN

When Can Set: Session startup (command line or config) only

Restricted: Your Site Administrator can restrict modification of this option

Optsave: PROC Optsave or command Dmoptsave will not save this option

NOTE: PROCEDURE OPTIONS a utilisé (Durée totale du traitement) :

temps réel 0.00 secondes

temps UC 0.00 secondes

 

25

26 %_eg_hidenotesandsource;

38

39

40 %_eg_hidenotesandsource;

43

 

Therefore, I think it should work.  Am I right?

If so, what can I do to make it working?

Regards,

 

Alain

ChrisBrooks
Ammonite | Level 13

Yes that confirms that you have privileges to shell out and run external commands so we can eliminate that from the equation. Have you tried the suggestions from @RW9 in his first reply to your post? That's exactly what I would do next.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4554 views
  • 1 like
  • 4 in conversation