Axio Research Generation of Appended Reports Architecture and

  • Slides: 31
Download presentation
Axio Research Generation of Appended Reports Architecture and Automation Bill Coar (billc@axioresearch. com)

Axio Research Generation of Appended Reports Architecture and Automation Bill Coar (billc@axioresearch. com)

Generation of Appended Reports Existing RTF reports MS Word SAS ODB C Automation Using

Generation of Appended Reports Existing RTF reports MS Word SAS ODB C Automation Using Existing Architecture Program Tracker Sweet SAS Code DD E Appended report with Table of Contents

The Goals • Develop a tool to append existing RTF files into a single

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 > Word.

The motivation for using SAS • • Base knowledge of to SAS > Word. RTF files Did you ever try append using Can use existing files. Word? and directory structures Can be automated via SAS macros Made available to all SAS users

The Schematic Sweet SAS Code ODB C Program Tracker MS Word SAS DD E

The Schematic Sweet SAS Code ODB C Program Tracker MS Word SAS DD E Existing RTF reports Appended report with Table of Contents

The Idea • Read in a list of RTF files from the program tracker

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

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

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

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

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

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

Update the dataset DATA X; set myxls. 'TFL Timelines$'n; length toc $150 pageno $100 bnum $5; bnum="BN"||left(put(_n_, z 3. )); Desired Text for Titles in Hyperlinks with TOC toc='{field {*fldinst HYPERLINK \l "'||bnum||'" }{fldrslt { cs 15cf 2 '||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

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="&DirpathOut"||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

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

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

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

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

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 Word. Basic commands to tell Word what to do

Open Word and Establish DDE Link data _null_; length fname $ 8; fname='tempfile'; rc=filename(fname,

Open Word and Establish DDE Link data _null_; length fname $ 8; fname='tempfile'; rc=filename(fname, 'C: Progra~1Micros~1Office 12WINWORD. EXE'); if (rc=0) and (fexist(fname)) then do; call system('start C: Progra~1Micros~1Office 12WINWORD. 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

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 Word. Basic commands – Within a data step – May/may

DDE Workhorse • Issuing Word. Basic commands – Within a data step – May/may not need information from input dataset • Create variables that consist of Word. Basic 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;

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='[File. Open. Name = "'||"&dir. Path. Outfilename. RTF"||'"]'; put cmd; * Go to the end of the document to begin inserting RTF files. ; cmd='[Endof. Document]'; put cmd; * Insert a new section for purposes of titles and footnotes. ; run; cmd='[Insert. Section. Break]'; put cmd;

DDE Workhorse • Setup page layout for remaining (imported) RTFs – cmd='[File. Page. Setup.

DDE Workhorse • Setup page layout for remaining (imported) RTFs – cmd='[File. Page. Setup. Top. Margin="1. 5 in". Bottom. Margin=". 5 in". Left. Margin=". 7 in". Right. Margin=". 7 in"]'; • For each report (use the toc dataset) – Insert a bookmark • cmd='[Set. Start. Of. Bookmark "Line" , "'||bnum||'"]'; – Insert the file • cmd='[Insert. File. Name="'||trim(file_name)||'". Range="". Link=0. Confirm. Conversions=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

DDE Workhorse • Save the document as a DOC file – Initial save for establishing page numbers • cmd='[File. Save. As. Name =' ||'"'|| filename ||'"' || ', . Format=0]'; • Update the To. C – Go to beginning • cmd='[Startof. Document]'; – Select the first table • cmd='[Table. Select. Table]'; – Update fields • cmd='[Update. Fields]';

DDE Workhorse • Re-save the Word document – cmd='[File. Save]'; • Close the document

DDE Workhorse • Re-save the Word document – cmd='[File. Save]'; • Close the document – cmd='[File. Close]'; • Close Word – cmd='[File. Exit]'; • Example

Some Pros/Cons • Pros: – (In theory) Can be standardized to work for any

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 F 9

Some Pros/Cons • Cons: – Code can be intimidating (though we are still <1

Some Pros/Cons • Cons: – Code can be intimidating (though we are still <1 KLOC) – 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 Word. Basic is supported any more

Some Pros/Cons • Cons: – Not sure if Word. Basic is supported any more • Excel Help file for word basic commands – http: //www. microsoft. com/downloads/en/details. aspx? familyid=1 a 2 4 b 2 a 7 -31 ae-4 b 7 c-a 377 -45 a 8 e 2 c 70 ab 2&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

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

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 TT 21 • Tong, C. “ODS RTF: Practical Tips”, NESUG 16, Paper AT 007 • Gilmore, J. “Using Dynamic Data Exchange with Microsoft Word”, SUGI 22, Paper 308

Axio Research Questions?

Axio Research Questions?

Membership Reports MEMBERSHIP Reports 1 Membership Reports REPORTSMembership Reports MEMBERSHIP Reports 1 Membership Reports REPORTS
Statutory Reports Research Overview Reports Research Statutory ReportsStatutory Reports Research Overview Reports Research Statutory Reports
Axio Research Idea to Application via SAS MacroAxio Research Idea to Application via SAS Macro
Setting the Scene These slides can be appendedSetting the Scene These slides can be appended
Research reports Research writing Types of research reportsResearch reports Research writing Types of research reports
Spontaneous Generation Spontaneous Generation Spontaneous Generation Spontaneous GenerationSpontaneous Generation Spontaneous Generation Spontaneous Generation Spontaneous Generation
www axio pl Fundusze dla maych i rednichwww axio pl Fundusze dla maych i rednich
www axio pl Preferencyjne finansowanie dla przedsiwzi innowacyjnychwww axio pl Preferencyjne finansowanie dla przedsiwzi innowacyjnych
Game Reports 1 GAME REPORTS PURPOSE Game ReportsGame Reports 1 GAME REPORTS PURPOSE Game Reports
Jasper Reports Jasper Reports Jasper Reports is aJasper Reports Jasper Reports Jasper Reports is a
Reports Reports Reports are difficult to define TheyReports Reports Reports are difficult to define They
Printing Reports Creating Reports q Reports are thePrinting Reports Creating Reports q Reports are the
Architecture Architecture Non EJB Architecture Architecture Non EJBArchitecture Architecture Non EJB Architecture Architecture Non EJB
Terrain Generation Terrain Generation Terrain Generation Random TerrainTerrain Generation Terrain Generation Terrain Generation Random Terrain
Generation titles P generation parents F 1 generationGeneration titles P generation parents F 1 generation
Neo Cortical Repository and Reports Database and ReportsNeo Cortical Repository and Reports Database and Reports
Forms and Reports Introduction Forms and reports areForms and Reports Introduction Forms and reports are
Reports and proposals Objective Introduction to reports andReports and proposals Objective Introduction to reports and
FFI Reports and Analysis FFI Reports and AnalysisFFI Reports and Analysis FFI Reports and Analysis
Creating Reports Understanding various types of reports andCreating Reports Understanding various types of reports and
CBISA Online Reports Instructions for Creating Reports andCBISA Online Reports Instructions for Creating Reports and
Completing Reports and Proposals 1 Finalizing Formal ReportsCompleting Reports and Proposals 1 Finalizing Formal Reports
Converting reports to Silverswitch and running VFP reportsConverting reports to Silverswitch and running VFP reports
CBISA Plus Reports Instructions for Creating Reports andCBISA Plus Reports Instructions for Creating Reports and