topic Re: Link to SAS tables from a Word report in SAS Programming

Link to SAS tables from a Word report

PGStats — Thu, 28 Mar 2013 17:10:18 GMT

Hi fellow Forum members,

I would like to link to SAS output tables from MS-Word documents. I tried creating the tables into a RTF document with anchors using ODS and then referring to these anchors from my Word report. That didn't work. I can create a hyperlink to the table in the RTF document but I can't make the table appear in my report. Can it be done?

Re: Link to SAS tables from a Word report

art297 — Thu, 28 Mar 2013 18:42:49 GMT

: from word, if you click on insert and then on the icon shown below, followed by "Text from File", you can insert rtf and html files into word documents:

Re: Link to SAS tables from a Word report

PGStats — Thu, 28 Mar 2013 19:01:16 GMT

Thanks Art for the detailled instructions. I tried that and it didn't include anything into my Word document (it didn't signal any error). Does it work for you? I'm using SAS 9.3 and Word 2007.

- PG

Re: Link to SAS tables from a Word report

Reeza — Thu, 28 Mar 2013 19:28:20 GMT

Hi PG,

Can you explain a bit more of what you're trying to do?

What do you mean by a SAS Table? Is it in another word document and what kind of link are you looking for.

Re: Link to SAS tables from a Word report

PGStats — Thu, 28 Mar 2013 19:36:10 GMT

A SAS table is the formatted version of an ODS dataset. I guessed the RTF version of a table would be the most compatible with Word.

I am looking for an updatable (dynamic?) link. If I redid the analysis in SAS, I would like to see the updated version in my Word report.

Re: Link to SAS tables from a Word report

Reeza — Thu, 28 Mar 2013 19:40:06 GMT

Is that still a SAS dataset, ie sas7bdat file?

You can embed an excel table into a word document. And if you update the source excel table via SAS either through a simple proc export, DDE or other manner it should update in Word as well.

Re: Link to SAS tables from a Word report

art297 — Thu, 28 Mar 2013 20:00:41 GMT

Yes it did work for me. I'm using Word 2010. However, it wouldn't update the document when the html or rtf document changed.

If you can attach one of the files, I'll see if it works on that particular file.

Re: Link to SAS tables from a Word report

art297 — Thu, 28 Mar 2013 20:26:31 GMT

: Try following the instructions shown in the following link: Automatically Updating Text in Many Documents

This actually links the files with the new document so that the document is updated when anything in the link changes. The instructions, basically, are just to:

1. Create a document containing the text you want to link to

2. Copy the text you would like to insert in your other documents

3. Open the document where you would like to insert the text

4. Place the cursor where you would like the text to appear

5. From the Edit menu, select Paste Special

6. From the Paste Special dialog box, select Paste Link

I tested it, at least in Word2010, by inserting an HTML document into the new document. I presume it should work with rtf as well.

Re: Link to SAS tables from a Word report

PGStats — Thu, 28 Mar 2013 21:11:29 GMT

Thanks for the suggestion Reeza, but a raw data table is not what I need to include in my report.

It's the formatted version of a SAS table that I want to link to, something like :

It would be part of a RTF file and pointed to within the file by an anchor name. I can bring such a table from the RTF output to my report with cut and paste but I would like to make the process automatic.

Re: Link to SAS tables from a Word report

PGStats — Thu, 28 Mar 2013 21:51:10 GMT

Hi Art,

Thanks for your time and ideas. Like so many half baked features in MS-Office, this one almost looks like it works. When I paste a link to my table, Word inserts a new bookmark in the RTF file. It call it OLE_LINK1. It doesn't use the bookmark that was inserted by SAS for the same table. If I edit the RTF file with Word, the edits will be carried over to my report. But this also means that when I update the RTF file by running my SAS program again, the inserted link (OLE_LINK1) is lost and the link is broken.

Re: Link to SAS tables from a Word report

Linlin — Thu, 28 Mar 2013 22:14:47 GMT

Hi PG,

Do you think mail merge would work?

Re: Link to SAS tables from a Word report

PGStats — Thu, 28 Mar 2013 22:37:17 GMT

I'm not familiar with mail merge. Can whole tables be inserted with it? - PG

Re: Link to SAS tables from a Word report

Linlin — Fri, 29 Mar 2013 00:02:48 GMT

Hi PG,

I tried. I can only insert a row from a table not the whole table. Sorry.

Re: Link to SAS tables from a Word report

art297 — Fri, 29 Mar 2013 01:08:21 GMT

: Methinks that is one for SAS to take care of, namely offering to maintain links when a file is replaced.

Re: Link to SAS tables from a Word report

Peter_C — Fri, 29 Mar 2013 01:55:31 GMT

could a hyperlink in the word doc open a SAS table in the SAS Universal Viewer, and would that be of adequate quality of presentation?

Re: Link to SAS tables from a Word report

Cynthia_sas — Fri, 29 Mar 2013 02:49:49 GMT

Hi,

With a SAS Stored Process and the SAS Add-in for Microsoft Office, you can insert stored process results into a Word doc (basically ODS output) and then tell the Add-in to refresh the output on open. And, if the data has changed since the last time the stored process was run, the new version of the data would populate the formatted stored process output in the Word doc. So that kind of functionality exists, but you would either need the Office Analytics suite or the Enterprise BI Server suite. Both include the SAS Add-in for Microsoft Office.

Otherwise, I'm not sure how you would do what you want. Sort of sounds like .NET or VB type of process -- an after the fact process where the SAS output is grabbed via Microsoft technology to update the Word document.

cynthia

Re: Link to SAS tables from a Word report

PGStats — Fri, 29 Mar 2013 03:21:09 GMT

Ouch! I was looking for a way to simplify my life :smileylaugh:!

Thanks all for your ideas! I think I'll quit on that one.

Re: Link to SAS tables from a Word report

bentleyj1 — Fri, 29 Mar 2013 14:47:16 GMT

I'm going to be presenting a paper at SGF in San Francisco next month on _reading_ Word documents into SAS and the process can be reversed to write SAS data to a Word document but I don't cover it. The trick is setting bookmarks in Word--such as bookmarking a table--and then running SAS so that it outputs data into the bookmarked table. You don't have to worry about how many rows are in the data set because a bookmarked table is dynamic in terms of rows.

I'd rather not post the paper here but if you send me your email address I'll send you an advance copy. john.bentley@wellsfargo.com

Re: Link to SAS tables from a Word report

Haikuo — Fri, 29 Mar 2013 15:31:10 GMT

LOL, PG, I hear ya! But as a matter of fact, the way that Cynthia suggested is fairly straightforward if you have those products installed. Do note that EBI platform etc are designed in such a way that those who do not have BASE SAS experience are able to pick them up quickly. You probably will be bored for lacking intelligence challenge when using them.

Haikuo

Re: Link to SAS tables from a Word report

Reeza — Fri, 29 Mar 2013 17:18:03 GMT

Here's what I tried and what worked and what did not work.

I created an excel file that I exported the SAS output to.

I created a new sheet in the excel file that I can format the table to look nice.

Save the document with the formatted sheet being the sheet on display on close.

In the Word Document, insert object, from file, with 'link to file' selected.

Save the word document.

Open the excel file and make changes and then close.

The word document asks to be updated when it opens and depending on the selection is updated.

Some issues - updates aren't instantaneous, it seems to take a full minute to refresh the link at least if both files are open.

Second, if the excel file is saved with a different tab active then that tab is displayed not the formatted one.

I can only see this working for one table though, if you're doing more than one table you'd need a more robust method. I'll be interested in seeing Johns solution.