SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Instead of DDE, tagsets or...

Reply
New User
Posts: 1

Instead of DDE, tagsets or...

[ Edited ]

Hi Guys,

I have a question. Currently I'm using DDE to copypaste data from one excel into another and for formatting stuffs in local SAS.Can someone help me to do this without DDE is SAS.

I tried tagsets excelxp too but when I tried to do it in a xlsm file the file become unusable. Please help me on this

Super User
Posts: 5,388

Re: SAS DDE

If you have specific problems with tagsets, please provide more information, such as SAS code used, any warning/error messages, Office version and bitness etc.

 

If you wish go towards an application design, I would recommend SAS Add-in for MS Office, and let a Stored Process update your spreadsheet.

Data never sleeps
Super User
Super User
Posts: 7,720

Re: SAS DDE

Hi,

 

This is a question & answer forum, not a training site.  If you have specific question then post them, with example test data (in the form of a datastep) and what the output should look like.  Tagsets.excelxp is quite powerful, but bear in mind that it generates XML output which Excel can read and interpret.  There is a newer version ods excel:

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

Which can create native XLSX files (which are ZIP files with various XML and other files within).

You can also get data out to Excel by using proc export, CSV, libname excel.

DDE is quite old now (decades), and doesn't work in some setups.  I would avoid it.  

What I would suggest is that if you are using Excel - in this case copy from one workbook to another - then it makes sense to use the native application - Excel + VBA.  It is a realy simple process to copy from one file to another in this manner:

Sub Copy()
  Dim ws1 As Worksheet
  Set ws1 = ThisWorkbook.Worksheets("<Your Sheetname>")
  ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub

No reason to go out to a third party app like SAS to do things like this.  You can apply all Excel functions within there, and you don't need to save the VBA to the output file, just save it to an empty Excel file, and load/close your other files.  

 

As a final note, XLSM = Macro Enabled Excel file - this is not the basic Excel sheet, but contains a binary component.  SAS won't write out to these very well (they are not open source).  I am not surprised you end up with a corrupt file, as stated above tagsets.excelxp writes out XML text to a file which Excel can read and parse, it doesn't update a file in place.  

 

Ask a Question
Discussion stats
  • 2 replies
  • 259 views
  • 0 likes
  • 3 in conversation