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

Managing SAS Add-in User Interface

Reply
Occasional Contributor
Posts: 11

Managing SAS Add-in User Interface

To maintain a controlled environment and also to meet 21 CFR Part 11 compliance I believe the items mentioned below will definitely help the Pharma industry and may be others also.

1) Ability to disable Save Password as part of the Login window and in Server Connections window.
2) Ability to disable SAS menu items particularly Active Data, Options and Server Connections.
3) Ability to insert tables/reports as form or object to prevent the user from editing the values.
N/A
Posts: 0

Re: Managing SAS Add-in User Interface

Thank you for your suggestions. Some of these options are being considered for a future release.

Are others interested in having tables that are display only available?
Occasional Contributor
Posts: 11

Re: Managing SAS Add-in User Interface

Thanks Jennifer.

Do you know if the ability to insert the table as a display is one of the item being considered for a future release? If so, do you have an approximate release date?

The reason is I am currently working on a project to optimize the building of CMC (chemistry, manufacturing, and controls) submission for IND, NDA ... to be released into production by end of this year.

We are planning to use SAS Add-in for MS Word to populate the tables/reports as part of the submission document to reduce the cycle time. One of the user requirement is that the inserted tables must be non-editable so additional QA need not be performed after document review cycle.

So, I am hoping if SAS Add-in can provide this functionality I do not have to come up work around to meet the requirement.
SAS Employee
Posts: 33

Re: Managing SAS Add-in User Interface

We have heard this request a couple of times before. There are, currently, no specific plans for the next version (which is probably a year or more away) to support some kind of read-only SAS output.

We have considered such a feature, but can't seem to integrate it with how Office works. The best I can come up with is some kind of check sum/hash code calculated from a table, graph, or other SAS output and saving that code in the document. The Add-In could on request check the contents of the output against the check sum to prove that the contents (the values and not the formatting) have not changed since the results were inserted. Would something like this meet your needs?
Occasional Contributor
Posts: 11

Re: Managing SAS Add-in User Interface

Sorry for the delay in responding and also for the lengthy posting.

The check sum method will definitely be helpful to minimize the QA after the review cycle. Couple of suggestions:
1) Option to calculate the check sum and to check the contents of the document triggered through STP. This would allow only super user or system admin to perform these tasks so there is better control over the process.
2) Identify only those tables/outputs that got modified during the review cycle using the check sum method. This would facilitate refreshing only the modified tables rather than the entire document.

Here is the work around I am working to implement this requirement. For most part this seems to be working but I haven’t done extensive testing yet. Any comments or feedback will be greatly appreciated. Also, please let me know if there are pitfalls I need to be aware of. Thanks in advance.

1) Using ODS RTF created an rtf document with the table (called before %stpbegin)
2) Using HAUTO/OLE methods copied the table and pasted as a picture and saved the document as Filtered Web Page. This creates a gif image of the table. (called before %stpbegin)
3) Using ODS tagsets.MSOffice2k created a table with a single row and inserted the image using preimage.

Notes/Observations:
1) Had to add sassrv to the users group to provide access to the file system.
2) Re-installed the object spawner service with –nonoxcmd option
3) This works only if HTML format is selected. The RTF format sets the _ODSDEST to rtf and ODS RTF accepts only jpg image and I didn’t have much luck in creating a jpg from a gif programmatically. Even when I forced the macro variable _ODSDEST to tagsets.msoffice2k for some reason no output was generated (no error in the log). (NEED HELP)
4) This approach is going to cost the company more SAS licensing cost, since our metadata/STP server is on a UNIX platform and OLE can be run only on a Windows platform. The proposal is to either install required base products + integration technology or base products + SAS/Connect on the Windows server.

Here is the program:

*ProcessBody;
%global _insertresultas;
/*
***To Test from a SAS Session***;
%let _insertresultas=image;
filename _webout temp;
*/
/********************************************
Location for temporary rtf file***
The gif file will be saved in a location
relative to this path.
*********************************************/
%let rtfDoc=\\rkenr314557700\d$\SAS\tmp\myrtf.rtf;
ods path work.mytemplate(update) sashelp.tmplmst(read);
/***************************Override Style****************************
Style is set to MyStyle. Requires cellspacing to be a minimum of 3 for
the image to capture lines.
*********************************************************************/
proc template;
delete styles.myStyle;
define style styles.myStyle;
parent=styles.rtf;
style header from headersandfooters /
background=white
foreground=black
cellspacing=3
font_size=2
font_weight=bold
;
style rowheader from rowheader /
background=white
foreground=black
cellspacing=3
font_size=2
font_weight=medium
;
style table from table /
background=white
borderwidth=1
cellspacing=3
font_size=2
frame=void
rules=all
;
end;
run;
%macro runResult;
options nodate nonumber;
title;
footnote;
data work.company;
set sashelp.company;
label
level2="Level2"
level1="Level1"
level5="Level5"
deptHead="Department Head"
level3="Level3"
level4="Level4"
job1="Job"
n="Count"
;
if _n_ <= 20 then output;
run;
/***************************Override Destination**********************
Destination should be tagsets.MSOffice2K equivalent to selecting
HTML in the Add-In option. This is needed due to ODS RTF requires
a jpg file instead of gif file. MS Word creates a gif file when
the document is saved as Filtered HTML. Need to revisit if
destination is other than MS Office.
*********************************************************************/
%if not %sysfunc(symexist(_ODSDEST)) %then
%global _ODSDEST;;
%let _ODSDEST=tagsets.MSOffice2K;
/***************************Override Stylesheet***********************
Style Sheet is set to blank.
*********************************************************************/
%if %sysfunc(symexist(_ODSSTYLESHEET)) %then
%let _ODSSTYLESHEET=;;
/***************************Override Style****************************
Style is set to MyStyle.
*********************************************************************/
%if %sysfunc(symexist(_ODSSTYLE)) %then
%let _ODSSTYLE=Styles.MyStyle;;
%if %lowcase(&_insertresultas) eq image %then %do;
%global imagefile;

ods noresults;
ods rtf file="&rtfDoc" style=&_ODSSTYLE;
proc template;
delete styles.myTableStyle;
define table styles.myTableStyle;
column
%let dsid=%sysfunc(open(work.company,i));
%do i=1 %to %sysfunc(attrn(&dsid,nvars));
%sysfunc(varname(&dsid,&i))
%end;
%let dsid=%sysfunc(close(&dsid));
;
end;
test data=work.company;
run;

ods rtf close;
ods results;

/***SCL Entry to create a gif image of the table***/
proc display c=openarch.callentries.getTableImage.scl batch;
run;

options mprint;

%stpbegin;
data work.class;
set sashelp.class;
keep sex;
label sex="";
sex='';
if _n_=1 then output;
run;

proc print data=work.class noobs split='*' label
style(table)={rules=groups cellspacing=0 frame=box}
style(header)={just=l protectspecialchars=on nobreakspace=on}
style(data)={preimage="&imagefile" cellwidth=100%}
;
run;

%stpend;
%let deleteRTF=y;
%let deleteHTM=y;
%let deleteIMG=n;

/***Delete temporary files***/
/***The gif file cannot be deleted until the image is inserted***/
/***Need to come up with a way to delete these files later***/
proc display c=openarch.callentries.deleteTempFile.scl batch;
run;
%end;

%mend runResult;

%runResult;
SAS Employee
Posts: 7

Re: Managing SAS Add-in User Interface

I have been playing with the ability to protect the stored process output placed in the word document. If we apply sections to the document before and after the stored process output we can protect that section. The protection must be removed from the document before a new stored process can be run. The functionality to set the sections, turn on the protection, and reset protection as the stored process is run can be handled with some VBA code. Is it possible to align the vba code with the call to the stored process? I am guessing this is difficult because the workspace server would rarely be on the same machine as the client machine. Any ideas on how to align a stored process call and calling some VBA?
SAS Employee
Posts: 33

Re: Managing SAS Add-in User Interface

I don't think there is a reliable way to align a stored process call to some VBA code via the stored process server.

That said, it is possible with the 2.1 version of the Add-in to catch an event upon refresh of any item (stored process, task, or SAS data in Excel). There is a detailed explanation with example VBA code in the online help. Look in the index for events->writing for Word. While it won't help to unprotect the section, you could use the event to protect the section.
SAS Employee
Posts: 7

Re: Managing SAS Add-in User Interface

I followed the documentation and wrote a small macro which turns protection on after the stored process result is streamed into the word document. I also created a macro which will insert a document section then open the SAS Report dialog box to select the stored process to run. Using these two VBA code sections together I am able to protect the table in Word.

VBA Method to add section and open the SAS Report Selection Dialog Box:
Sub RunSASSTP()

Dim doc As Document

Set doc = ActiveDocument
If doc.ProtectionType <> wdNoProtection Then doc.Unprotect ("Test")

Selection.InsertBreak Type:=wdSectionBreakContinuous

Set STPCtrl = CommandBars("SAS Analysis Tools").Controls(4)

STPCtrl.Execute


End Sub

Code to Protect the document after Stored Process Run
Private Sub sas_ItemUpdated(ByVal refreshableObject As Variant)

Dim curSec As Integer

'find the current section
curSec = ActiveDocument.Sections.Count

If curSec = 2 Then
ActiveDocument.Sections(curSec - 1).ProtectedForForms = False
End If

MsgBox ("Current Section: " + Str(curSec))

ActiveDocument.Sections(curSec).ProtectedForForms = True

Selection.InsertBreak Type:=wdSectionBreakContinuous
ActiveDocument.Sections(curSec + 1).ProtectedForForms = False


ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="Test"

End Sub

Once the document is protected macros can not be called using Alt+8 so I placed a button on the command bar to call the RunSASSTP macro. I am now able to add and protect multiple stp outputs in a word document.
SAS Employee
Posts: 33

Re: Managing SAS Add-in User Interface

Todd,

That's a clever way around the protection issue. I'm glad that the results updated event met your need.
N/A
Posts: 0

Re: Managing SAS Add-in User Interface

Can you please send me the link ( URL)for online documentaiton
SAS Employee
Posts: 33

Re: Managing SAS Add-in User Interface

The Add-in's online documentation is only available via the Add-in as online help. Given that, from the properties window on the main help page, I do see a URL: mk:@MSITStore:C:\Program%20Files\SAS\Add-In%20for%20Microsoft%20Office\SASAMOHelp.chm::/Results/Events.htm.
N/A
Posts: 0

Re: Managing SAS Add-in User Interface

Hi,
I created a word template with protection, now I want users to use this template whenever they open SAS reports from word, How can we do that? any idea?
SAS Employee
Posts: 7

Re: Managing SAS Add-in User Interface

From my experience one must unprotect the document in order for the AMO results to be surfaced.
I protect the section returned from AMO but must unprotect the entire document to allow another stored process to complete.
Post a Question
Discussion Stats
  • 12 replies
  • 471 views
  • 0 likes
  • 4 in conversation