Date:         Fri, 8 Feb 2008 10:32:42 -0500
Reply-To:     Randy Herbison <RandyHerbison@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Randy Herbison <RandyHerbison@WESTAT.COM>
Subject:      Re: Reports using PUT statements but outputting via ODS
Comments: To: paulasims2004@hotmail.com
In-Reply-To:  <TIPqj.9076$J41.2310@newssvr14.news.prodigy.net>
Content-Type: text/plain; charset="us-ascii"

Paula,

If you have to use SAS to recreate the report format, then I think the
experimental ODSOUT data step object, as suggested by data _null_, may
be the best choice.

Much of the form can be created using SAS TITLE statements and inline
formatting.  The images and boxes would present a challenge.  My Westat
colleague, Rick Mitchell, has written a paper that demonstrates the
technique.  I don't have a link to the paper right now, but I can get it
if you are interested

I might be tempted to use a mail merge.  It can be done with WORD using
its mailmerge feature.

You can also use WORD bookmarks and SAS to populate the form using DDE
or OLE Automation.  The setup can be much easier than a real WORD
mailmerge.  I prefer OLE Automation, because it is more flexible and
robust than DDE.  SAS's implementation of OLE Automation is part of
SAS/AF.

Here's an example SAS/AF (SCL) program for populating WORD bookmarks.
(Windows/WORD experts please excuse the poor VBA).  The technique uses a
single WORD file as a template, populates the WORD file with SAS data,
saves the WORD file under a new name.  The process is repeated for each
row in the SAS table.  The output format is WORD, but it would be easy
to change to RTF (and/or PDF if you can write PDF files with WORD).

The calling macro follows the SCL.

-Randy

* This version of SAS2WORD will be used in a macro;

import sashelp.fsp;

declare hauto       WORD,
        hauto       documents,
        hauto       document,
        hauto       bookMarks,
        hauto       activeDocument,
        hauto       bookMarkItem,
        hauto       selection,

        num         bookMarkVarnum,
        num         outputFilenameVarnum,
        num         bookMarkExist,
        num         value,
        num         false=0,
        num         cmdList,
        num         rc,

        char(32)    bookMark,
        char(2048)  bookMarkList,
        char(1)     vartype,
        char(32767) text,
        char(1024)  WORDinput,
        char(1024)  outputFolder,
        char(1024)  outputFilename,
        char(32)    outputFilenameVar
;

array extFiles [2] outputFolder WORDinput;

init:

* Get the AFA command arguments;
cmdList=getniteml(envlist('L'),'_CMDLIST_');

WORDinput=getnitemc(cmdlist,'WORDinput');
SASinput=getnitemc(cmdlist,'SASinput');
outputFolder=getnitemc(cmdlist,'outputFolder');
outputFilenameVar=getnitemc(cmdlist,'outputFilenameVar');
bookMarkList=getnitemc(cmdlist,'bookMarks');

* Make sure external files exist;
do i=1 to 2;
  if not fileexist(extFiles{i}) then do;
    put "ERROR:  " extFiles{i} "doesn't exist!" ' external file' i=;
    return;
  end;
end;

* Make sure SAS input exists;
if not exist(SASinput) then do;
  put "ERROR:  " SASinput "doesn't exist!" '  sasinput';
  return;
end;

* Make sure SAS input is open and required variables exist;
dsid=open(SASinput);
if dsid < 1 then do;
  put "ERROR:  Can't open " SASinput "!";
  return;
end;
else do;
  i=1;
  * Check for existence of bookmarked variables;
  do while (scan(bookMarkList,i) ne ' ');
     bookMark=scan(bookMarkList,i);
     bookMarkVarnum=varnum(dsid,bookMark);
     if not bookMarkVarnum then do;
       put bookMark "isn't on " SASinput "!";
       dsid=close(dsid);
       return;
     end;
     i + 1;
  end;
  * Check for existence of Output folder variable;
  if not varnum(dsid,outputFilenameVar) then do;
    put outputFilenameVar "isn't on " SASinput "!";
    dsid=close(dsid);
    return;
  end;
end;

* Make sure we have a folder delimiter;
if substrn(reverse(outputFolder),1,1) ne '\' then
outputFolder=cat(outputFolder,'\');

start=time();

* Start WORD;
WORD=_neo_ hauto(0, 'Word.Application');

WORD._setProperty('Visible',false);

* Make sure WORD is running;
if WORD < 1 then do;
  put 'ERROR:  WORD failed to open!';
  return;
end;

WORD._getProperty('Documents', documents);


* Open the master document;
documents._compute('Open',WORDinput, document);
document._getProperty('Bookmarks', bookMarks);

outputFilenameVarnum=varnum(dsid,outputFilenameVar);

nlobs=attrn(dsid, 'NLOBS');

do i=1 to nlobs;

  rc=fetchobs(dsid, i);

  *Populate the bookmarks;
  j=1;
  do while (scan(bookMarkList,j) ne ' ');

     bookMark=scan(bookMarkList,j);
     bookMarkVarnum=varnum(dsid,bookMark);

     vartype=vartype(dsid,bookMarkVarnum);
     if vartype='N' then do;
       value=getvarn(dsid,bookMarkVarnum);
       text=left(put(value,best32.));
     end;
     else text=getvarc(dsid,bookMarkVarnum);

     link addText;
     j + 1;
  end;

  outputFilename=outputFolder || '\' ||
getvarc(dsid,outputFilenameVarnum) || '.doc';

  document._do('SaveAs', outputFilename);


end;

rc=close(dsid);

WORD._getProperty('ActiveDocument', activeDocument);
activeDocument._do('Close', false);

WORD._do('Quit');
WORD._term();

end=time();
elapsed=put(end - start,time.);
put 'Elapsed time=' elapsed;

return;

addtext:
bookMarks._compute('exists', bookMark, bookMarkExist);
if bookMarkExist then do;
  bookMarks._compute('Item', bookMark, bookMarkItem);
  bookMarkItem._do('Select');
  WORD._getProperty('Selection', selection);
  selection._do('delete');
  selection._do('TypeText', text);
end;
else put "ERROR:  " bookMark "isn't a bookMark in " WORDinput "!";
return;

*** Macro user interface ***;
%macro mailMerge(
                  WORDinput=,
                  SASinput=,
                  outputFolder=,
                  outputFilenameVar=,
                  bookMarks=
                 )
;

dm 'afa c=__afapps.sas2word.main.scl

    WORDinput=&WORDinput
    SASinput=&SASinput
    outputFolder=&outputFolder
    outputFilenameVar=&outputFilenameVar
    bookMarks=&bookMarks'

;

%mend mailMerge;

%mailmerge(
            WORDinput="c:\temp\test.doc",
            SASinput="sashelp.class",
            outputFolder="c:\temp\test",
            outputFilenameVar=name,
            bookMarks="name age sex height weight"
           )



-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Paula Sims
Sent: Thursday, February 07, 2008 10:09 PM
To: sas-l@uga.edu
Subject: Reports using PUT statements but outputting via ODS


Hello all,
I've been trying to find samples of code to solve this issue but I
can't.

I have been asked to recreate a form and populate it with the
appropriate data. That's fine and I used to do something similar on the
mainframe using PUT @ statements and column counting. However, what I
would like this time is to do something similar but output to either PDF
or RTF. My data is formatted such that each row would be self-contained
withing the page, next row - next page.

Although this is not the form I need to re-create, the format is similar
  to page 1 of this form.
http://www.geneva.il.us/Permits/TempHandicappedParkingCard.pdf


Do anyone have any sample code or could direct me where to find such
code?

Thanks

Paula