X4ML commands for DDE with MS Excel

[Reeza]

Won't ODS Excel in 9.4 be preferable in the long run?

Sadly the graphs are graphics not actual Excel Graphs but getting closer every version...

[art297]

: I think that depends upon who the user is. I wanted some capabilities now, rather than waiting for some time in the future which may never come. That is what motivated us to write the macro.

If you have the time, I'd be interested in seeing a comparison of proc export, ODS Excel, and the macro. Like , I'd be interested in seeing resulting file sizes, processing times, and time required to open the resulting files.

[Reeza]

I don't have SAS 9.4 only tested it through the Analytics U version, which I don't think would be a fair comparison for processing times.

[jakarman]

The exportxl macro of Arthur will not run in the UE edition. It will also not run in a lot of other environments. Not in EGuide BI/DI batch.

The reason is the usage of the Windows statement. That one will only run in a interactive environment using the DMS interface.   
Got that solved and now it is somewhere trying to do  pop-up in the clipbrd step, that dumps again. For the ones liking that kind of dumps. sasmotif is mos likely related to the X11  (dms) or Unix popups

ERROR:  An exception has been encountered.

Please contact technical support and provide them with the following traceback information:

The SAS task name is [DATASTEP]

Segmentation Violation

Traceback of the Exception:

/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sas() [0x52635e]

/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sas() [0x41ebee]

/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x144) [0x7f67d4b02514]

/lib64/libpthread.so.0(+0xf710) [0x7f67d5d25710]

/opt/sasinside/SASHome/SASFoundation/9.4/dbcs/sasexe/sasmotif(wuxpclr+0x61) [0x7f676af2d3d1]

/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/sasxbamb(yxoopn+0x15a) [0x7f676b6e4f5a]

/opt/sasinside/SASHome/SASFoundation/9.4/dbcs/sasexe/sasxkern(yxropen+0x96) [0x7f67c90011a6]

/opt/sasinside/SASHome/SASFoundation/9.4/sasexe/saswudsf(efopen+0x2c3) [0x7f676dff5103]

[0x7f676e31e690]

[art297]

: The windows statement will only be called if:

  the parameter range is set to either y or Y and/or

  the parameter template is set to either y or Y and/or

  the parameter templatesheet is set to either y or Y

The functionality was included so that the macro could be called via a point-and-click SAS abbreviation where the settings for any one or all of those parameters might be needed but not specifiable until run time. Otherwise the calls aren't made and shouldn't pose a problem.

The popup that appears when the VB script is run can be eliminated by setting your SAS noxwait and noxsync options.

The macros biggest limitation, in my opinion, is that it will only work on Windows compatible systems.

[RW9]

Hi,

I haven't read up on 9.4 excel tagset as yet.  From my side I would like to see an easier way of writing actual Open Office document format, i.e. something like:

ods OODOC file="...xlsx"; /* Switch xlsx/docx/pptx etc */

I can already write basic OO files from within SAS as its not particularly difficult.  The main difficulty comes from getting the items in the right places.

So whilst I would go ahead with that on my own anyways, it maybe a thought for a future enhancement.

[jakarman]

Rw9 you are quite right on the open Office formats. Doe me remember of: https://www.linkedin.com/groupItem?view=&gid=70702&type=member&item=5821265731240628226&commentID=-1...  (Updating an Excel Template from a Unix Environment) SAS professional form at linkedin.

The ODS Excel could be a start but looks to me not the real OO data connection (Oasis defined zipped XML files).  

The functionality using a generate VBS looks to be different. It should be able to run in more environments when the code would be better structured and more modulair based.

++ The error I got is on clipbrd access. That only makes sense in a DMS environment.  This could be the dump with UE.

     That interface is as for I know was Windows limited but seeing it back in generic. SAS(R) 9.4 Statements: Reference, Third Edition.  

++ Saving that VB script to a temp location makes it inaccessible for a download. When that would a free parameter-file it gets better controlled

Just trying if those changes are possible and whether I can Understand that clipbrd part why it is running and dumping..  

[art297]

: the macro uses the filename function (SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition) rather than the filename statement.  One may have to include some host options for non-windows environments. However, the SAS documentation doesn't mention anything about using the clipbrd access method with the function. Randy (I believe) discovered and suggested its use, and we included it because it gets around the limitations of the filename statement when using the clipbrd access method.

The script is saved to the work directory, so it is easy to retrieve if desired.

[PhilfromGermany]

Arthur, thanks for suggesting your macro. Will this macro run as part of an automated batch on Windows Server 2008? Or does it really require interactive mode?

[art297]

Phil: As described in the paper supporting the macro it can be run either way as long as you don't introduce an incompatible parameter. For example, if your chosen method of running doesn't allow a 'window' statement, don't set range, template or templatesheet to a value of Y.

[PhilfromGermany]

Arthur, I never got the macro to work. I was finally able to use DDE to run Excel Macros, but exporting data via the old or the new sas to xl macros hasn't worked for me. I'm using SAS Enterprise Guide 6.1 and MS Excel 2010. Isn't there a DDE snippet somewhere to simply export data set "A" to an existing workbook "B"?

[art297]

Phil: If you are using EG off of a server then the macro may not work, or will work quite a bit less efficiently than using the libname access method as suggested.

Also, as I just mentioned in another post, there are some EG limitations you'd have to circumvent in order to be able to use the macro (see: Using the X and SYSTASK commands from SAS Enterprise Guide - The SAS Dummy)

[jakarman]

Use the libname to Excel access method if you can. I posted that before.

[PhilfromGermany]

Yes, but doesn't that require ACCESS to PC Files?

[jakarman]

You are right on that. The macro of Arthur was nice until I hit that clipboard.

There is an other way to communicate data, that is using XML.  Just tested (UE) generating a XML file an import that into Excel. The disadvantage can be the sizing/overhad as it is not zipped.

This one works:
libname trans xml '/folders/myfolders/test/trans.xml' xmltype=generic;

data trans.class;
   set sashelp.class;
run;

This way of XML (generic) support only one table and that is importable well in Excel. (ignore description tab)

Would be a question whether Arthurs macro could be updated to use this as alternative for clipbord and possible for many ranges.