Axio research
This presentation is the property of its rightful owner.
Sponsored Links
1 / 31

Axio Research PowerPoint PPT Presentation


  • 53 Views
  • Uploaded on
  • Presentation posted in: General

Axio Research. Generation of Appended Reports Architecture and Automation Bill Coar ([email protected]). Generation of Appended Reports. SAS. Existing RTF reports. ODBC. Automation Using Existing Architecture. Program Tracker. MS Word. Appended report with Table of Contents.

Download Presentation

Axio Research

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Axio research

Axio Research

Generation of Appended

Reports

Architecture and Automation

Bill Coar

([email protected])


Generation of appended reports

Generation of Appended Reports

SAS

Existing RTF reports

ODBC

Automation Using Existing Architecture

Program Tracker

MS Word

Appended report with Table of Contents

Sweet SAS Code

DDE


The goals

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

The motivation for using SAS

  • Base knowledge of SAS > Word

  • Can use existing files and directory structures

  • Can be automated via SAS macros

  • Made available to all SAS users

Did you ever try to append RTF files using Word?


The schematic

The Schematic

Sweet SAS Code

Program Tracker

ODBC

SAS

MS Word

Existing RTF reports

DDE

Appended report with Table of Contents


The idea

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

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

Reading the Program Tracker

  • Establish a link to the program tracker

    • Do not need to create an ODBC connection outside of SAS

  • Side notes:

    • File must not be in use by others

    • Some common format is desirable to standardize across projects

libnamemyxlsodbc

required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};

dbq=&DIRPATH.\SampleProgram Tracker.xlsx";


Reading the program tracker1

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 idea1

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

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 dataset1

Update the dataset

DATA X;

set myxls.'TFL Timelines$'n;

length toc $150 pageno $100 bnum $5;

bnum="BN"||left(put(_n_,z3.));

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;

Desired Text for Titles in Hyperlinks with TOC


Consider defensive programming

Consider Defensive Programming

  • Verify the output file actually exists

    • Output warnings in log file

    • Only keep records where the files exist

Specify full directory path where RTF files exist

Data errata;

set X

length opath $200;

opath="&Dirpath\Out\"||file_name;

fe=fileexist(opath);

if fe=0;

put "WARN""ING - Some output files do not exist. Please verify";

put opath=;

Run;


The idea2

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

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 rtf1

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 idea3

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

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

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;

rc=filename(fname);

t=sleep(1);

run;

filename testitdde 'winword|system';


Dde workhorse1

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 workhorse2

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

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

cmd='[InsertSectionBreak]';

put cmd;

run;


Dde workhorse3

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 workhorse4

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 workhorse5

DDE Workhorse

  • Re-save the Word document

    • cmd='[FileSave]';

  • Close the document

    • cmd='[FileClose]';

  • Close Word

    • cmd='[FileExit]';

  • Example


Some pros cons

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 cons1

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 cons2

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=1a24b2a7-31ae-4b7c-a377-45a8e2c70ab2&displaylang=en

      • Issues with Windows 7 and Vista reading Windows Help files

        • http://support.microsoft.com/kb/917607


Final remarks

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

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 research1

Axio Research

Questions?


  • Login