Transcript Generation of Appended - Denver SAS Users Group

Axio Research
Generation of Appended
Reports
Architecture and Automation
Bill Coar
([email protected])
Generation of Appended Reports
Existing RTF
reports
SAS
ODBC
Automation Using
Existing Architecture
MS Word
Program
Tracker
Sweet SAS
Code
DDE
Appended
report with
Table of
Contents
The Goals
• Develop a tool to append existing RTF files
into a single report that contains a
hyperlinked (pseudo) table of contents
– Assume common page layout for each RTF
• Use existing infrastructure
• Independent of user
• (Easily) automated
The motivation for using SAS
•
•
•
•
Base
knowledge
of to
SAS
> WordRTF files
Did you
ever try
append
using
Can use existing
filesWord?
and directory structures
Can be automated via SAS macros
Made available to all SAS users
The Schematic
Sweet SAS Code
ODBC
Program
Tracker
MS Word
SAS
Existing RTF
reports
DDE
Appended
report with
Table of
Contents
The Idea
• Read in a list of RTF files from the program
tracker
• Update the dataset
– Bookmarks
– RTF code
• ODS to RTF for Table of Contents
• DDE to communicate with Word
– Open and Append each RTF
– Final updating and saving
The Program Tracker
• One record per output
– Necessary for Table of Contents
– Not the same as one program per output
• Columns for:
–
–
–
–
Output file name
TLF number for sorting
TLF text to be displayed in hyperlink
If appropriate, sub-setting columns
• Example
Reading the Program Tracker
• Establish a link to the program tracker
– Do not need to create an ODBC connection
outside of SAS
libname myxls odbc
required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
dbq=&DIRPATH.\Sample Program Tracker.xlsx";
• Side notes:
– File must not be in use by others
– Some common format is desirable to standardize
across projects
Reading the Program Tracker
DATA X;
set myxls.'TFL Timelines$'n;
* Insert some sweet SAS code for displaying RTF codes
(hyperlinks and bookmarks) as well as sorting variables.
Run;
The Idea
• Read in a list of RTF files from the program
tracker
• Update the dataset
– Bookmarks
– RTF code
• ODS to RTF for Table of Contents
• DDE to communicate with Word
– Open and Append each RTF
– Final updating and saving
Update the dataset
• Create a bookmark for each file
– To be inserted just before the RTF is appended
• Create sorting variables based on TLF types
and numbers
– Many alternatives for sorting
• Two columns for display
– RTF code for hyperlink
– RTF code for cross-reference to bookmark for
page numbering
Update the dataset
DATA X;
set myxls.'TFL Timelines$'n;
length toc $150 pageno $100 bnum $5;
bnum="BN"||left(put(_n_,z3.));
Desired Text for
Titles in Hyperlinks
with TOC
toc='{\field {\*\fldinst HYPERLINK \\l "'||bnum||'" }{\fldrslt {
\cs15\cf2 '||trim(ttl)||' }}}';
pageno='{\field {\*\fldinst PAGEREF '||bnum||' \\h }}';
* Insert code to create some sorting variables.
Run;
Consider Defensive Programming
• Verify the output file actually exists
– Output warnings in log file
– Only keep records where the files exist
Data errata;
set X
length opath $200;
Specify full
directory path
where RTF
files exist
opath="&Dirpath\Out\"||file_name;
fe=fileexist(opath);
Run;
if fe=0;
put "WARN""ING - Some output files do not exist. Please verify";
put opath=;
The Idea
• Read in a list of RTF files from the program
tracker
• Update the dataset
– Bookmarks
– RTF code
• ODS to RTF for Table of Contents
• DDE to communicate with Word
– Open and Append each RTF
– Final updating and saving
ODS to RTF
• Proc report using ODS to RTF
– Eventually be the first page of the report
– Contains the (eventual) table of contents
•
•
•
•
Consider standard titles/footnotes
Turn the results viewer off
Close the RTF destination
Decide on orientation
ODS to RTF
• The RTF created:
– Should see the hyperlinks, but not page numbers
since the bookmarks needed for the referencing
do not yet exist
– Is the starting point to begin appending the
individual reports
• Example
The Idea
• Read in a list of RTF files from the program
tracker
• Update the dataset
– Bookmarks
– RTF code
• ODS to RTF for Table of Contents
• DDE to communicate with Word
– Open and Append each RTF
– Final updating and saving
DDE Workhorse
• Open Word (from within SAS)
• Establish DDE Link
• Dynamic Data Exchange (DDE)
– SAS to communicate with MS Word through an
established link
– Use WordBasic commands to tell Word what to do
Open Word and Establish DDE Link
data _null_;
length fname $ 8;
fname='tempfile';
rc=filename(fname,'C:\Progra~1\Micros~1\Office12\WINWORD.EXE');
if (rc=0) and (fexist(fname)) then do;
call system('start C:\Progra~1\Micros~1\Office12\WINWORD.EXE');
end;
run;
rc=filename(fname);
t=sleep(1);
filename testit dde 'winword|system';
DDE Workhorse
• Open the RTF that contains the TOC
• (Setup page layout for remaining (imported)
RTFs)
• For each report
– Insert a bookmark
– Insert the file
– Insert section break
• Save the document as a DOC file
– Initial save for establishing page numbers
DDE Workhorse
• Issuing WordBasic commands
– Within a data step
– May/may not need information from input dataset
• Create variables that consist of WordBasic
commands
• Put the commands to the filename that
defines the DDE link
Example: Open the RTF file with
initial table of contents
data null;
file testit;
length cmd $200;
* Open the initial RTF that contains the table of contents.;
cmd='[FileOpen.Name = "'||"&dirPath.\Out\filename.RTF"||'"]';
put cmd;
* Go to the end of the document to begin inserting RTF files.;
cmd='[EndofDocument]';
put cmd;
* Insert a new section for purposes of titles and footnotes.;
run;
cmd='[InsertSectionBreak]';
put cmd;
DDE Workhorse
• Setup page layout for remaining (imported) RTFs
– cmd='[FilePageSetup .TopMargin="1.5 in" .BottomMargin=".5 in"
.LeftMargin=".7 in" .RightMargin=".7 in"]';
• For each report (use the toc dataset)
– Insert a bookmark
• cmd='[SetStartOfBookmark "\Line" ,"'||bnum||'"]';
– Insert the file
• cmd='[InsertFile .Name="'||trim(file_name)||'" .Range=""
.Link=0 .ConfirmConversions=0]';
– Insert a section break
Variables in the input dataset used for the proc report/table
of contents
DDE Workhorse
• Save the document as a DOC file
– Initial save for establishing page numbers
• cmd='[FileSaveAs.Name =' ||'"'|| filename ||'"' || ',
.Format=0]';
• Update the ToC
– Go to beginning
• cmd='[StartofDocument]';
– Select the first table
• cmd='[TableSelectTable]';
– Update fields
• cmd='[UpdateFields]';
DDE Workhorse
• Re-save the Word document
– cmd='[FileSave]';
• Close the document
– cmd='[FileClose]';
• Close Word
– cmd='[FileExit]';
• Example
Some Pros/Cons
• Pros:
– (In theory) Can be standardized to work for any
project if trackers are consistent
• Eliminate the need for project specific Word macros
– Allows for re-number of tables w/o change macro
code
– Less human intervention
– Can be modified to allow for multiple reports per
RTF file (bookmarks created by SAS)
– Can update page numbers if post processing
• Print preview, or Cntrl-A then F9
Some Pros/Cons
• Cons:
– Code can be intimidating (though we are still
<1KLOC)
– Windows issues if mistakes are made
• Application hangs…
– Possible issues with DDE
• Tmpdde in word macros
• Corrupt windows profile
Some Pros/Cons
• Cons:
– Not sure if WordBasic is supported any more
• Excel Help file for word basic commands
– http://www.microsoft.com/downloads/en/details.aspx?familyid=1a2
4b2a7-31ae-4b7c-a377-45a8e2c70ab2&displaylang=en
• Issues with Windows 7 and Vista reading Windows Help
files
– http://support.microsoft.com/kb/917607
Final Remarks
• It seems to work so far
– Used successfully for inserting up to 100 RTF files
(>1000 pages)
• Many of the Word Basic commands are
straightforward
– Commands executed interactively to better
understand functionality
• Links maintained when converted to PDF with
Adobe writer
– Not sure about free PDF writers
References
• Shannon, D. “To ODS RTF and Beyond”, SUGI 27,
Paper 1-27
• Osowski, S., Fritchey, T. “Hyperlinks and Bookmarks
with ODS RTF”, Pharmasug 2006, Paper TT21
• Tong, C. “ODS RTF: Practical Tips”, NESUG 16,
Paper AT007
• Gilmore, J. “Using Dynamic Data Exchange with
Microsoft Word”, SUGI 22, Paper 308
Axio Research
Questions?