topic Getting extra row when creating a table using RTF in ODS and Base Reporting

Getting extra row when creating a table using RTF

supp — Tue, 25 Oct 2011 04:22:18 GMT

I am trying to create a form letter using the RTF tagset, proc template and data _null_. I am trying to create a table that will put a customers address in the upper left hand corner in this format:

FirstName LastName

Address Line 1

City State Zip

The problem I am having is my program produces output that looks like this when I open it in word (extra blank row?):

FirstName LastName

Address Line 1

City State Zip

Any suggestions? I am using SAS 9.1.3 on a IBM mainframe (z/OS).

Here is the code I am using:

data file1;

input @1 fname $10.

@11 LNAME $15.

@28 addr1 $10.

@39 city $15.

@55 state $2.

@58 zip $5.;

datalines;

firstname lastname line 1 city MN 55378

;

PROC TEMPLATE;

DEFINE STYLE TEXTSTYLE;

PARENT=STYLES.RTF;

STYLE TABLE FROM OUTPUT /

RULES = NONE FRAME = VOID;

END;

DEFINE TABLE REPORTS.ADDRESS;

COLUMN FNAME LNAME ADDR1 CITY STATE ZIP;

DEFINE COLUMN FNAME;

PRINT_HEADERS=OFF;

JUST=LEFT;

STYLE=CELL{CELLWIDTH=1IN};

END;

DEFINE COLUMN LNAME;

PRINT_HEADERS=OFF;

JUST=LEFT;

STYLE=CELL{CELLWIDTH=1IN};

GLUE = -1;

END;

DEFINE COLUMN ADDR1;

PRINT_HEADERS=OFF;

JUST=LEFT;

STYLE=CELL{CELLWIDTH=1IN};

GLUE = -1;

END;

DEFINE COLUMN CITY;

PRINT_HEADERS=OFF;

JUST=LEFT;

STYLE=CELL{CELLWIDTH=1IN};

END;

DEFINE COLUMN STATE;

PRINT_HEADERS=OFF;

JUST=LEFT;

STYLE=CELL{CELLWIDTH=.5IN};

END;

DEFINE COLUMN ZIP;

PRINT_HEADERS=OFF;

JUST=LEFT;

STYLE=CELL{CELLWIDTH=.75IN};

END;

RUN;

OPTIONS NODATE NONUMBER NOCENTER PAPERSIZE=LETTER;

TITLE;

FOOTNOTE;

ODS ESCAPECHAR'@';

ODS RTF FILE = FTP

STYLE = TEXTSTYLE

BODYTITLE

RS = NONE;

DATA _NULL_;

SET FILE1;

BY FNAME;

IF _N_ = 1;

FILE PRINT ODS=(TEMPLATE='REPORTS.ADDRESS'

COLUMNS=(FNAME LNAME ADDR1 CITY STATE ZIP));

PUT _ODS_;

RUN;

ODS RTF CLOSE;

Getting extra row when creating a table using RTF

art297 — Tue, 25 Oct 2011 05:26:23 GMT

Are you planning on having SAS create the form letter or is that going to be accomplished with another program (like Word)? If it is the latter, I'd skip the ODS stuff and simply export either an Excel file or a CSV file which most word processors will accept.

Re: Getting extra row when creating a table using RTF

supp — Tue, 25 Oct 2011 15:02:48 GMT

I am trying to create the entire letter in SAS, so I can create them dynamically based on my data source. What I posted was a snippet of the letter to illustrate the problem I am having.

The adress table in the form letter is six columns:

1.) First Name

2.) Last Name

3.) Address line 1

4.) City

5.) State abbr

6.) Zipcode

I need the table to wrap or break at defined locations, after last name and before address line1 and after address line 1 and before city. In my template I used glue -1 to force the break but it seems I get an extra row. After doing some google searching and looking around the forum's I can't seem to find a setting to control this extra row.

In case it helps I was able to edit the RTF generated and was able to remove "\par" after each break and that eliminated the extra row.

Re: Getting extra row when creating a table using RTF

Tom — Tue, 25 Oct 2011 15:41:19 GMT

Did you look at this article about using SAS to write form letters?

http://www.lexjansen.com/mwsug/2009/stats/MWSUG-2009-D09.pdf

Getting extra row when creating a table using RTF

Cynthia_sas — Tue, 25 Oct 2011 16:23:28 GMT

In addition to the technique discussed in the attached link, what I have done to create form letters, is to

1) design my letter in Microsoft Word, with Word merge fields

2) create my data as a CSV file and make sure that the column names in the first row match the Word field names

3) Once the CSV file is created, go into Word and start the merge process by using the CSV file as input to the merge (you can automate this part in Word).

There's a paper by Vince DelGobbo that describes making mailing labels using a similar technique:

http://analytics.ncsu.edu/sesug/2005/CC05_05.PDF

In the new report interface with ODS and the DATA step (still experimental in 9.3), you will be able to generate form letters directly from the DATA step. For more information, refer to this web site and look at the paper that is linked on the site:

http://support.sas.com/rnd/base/datastep/dsobject/index.html

cynthia

Getting extra row when creating a table using RTF

supp — Tue, 25 Oct 2011 16:28:21 GMT

Thanks Cynthia! Your suggestion about creating the CSV file and doing the mail merge may be my best option. It just feels that if I was able to generate the correct RTF I could skip that step and make my process more efficient.

Getting extra row when creating a table using RTF

supp — Tue, 25 Oct 2011 16:34:20 GMT

Thanks for the link Tom. I did read that paper and thought it was a viable option. I aslo read this paper:

http://www.quintiles.com/elements/media/inthenews/creating-customized-patient-profiles-using-sas-ods-rtf-and-proc-template.pdf

It seems the two papers used slightly different approaches. I opted to use proc template to define my tables as it seemed I would have enough control to do what I needed, instead of putting the whole letter into one variable. Since I can't seem to find a way to fix this 'extra row' problem perhaps I was wrong.

Re: Getting extra row when creating a table using RTF

Cynthia_sas — Tue, 25 Oct 2011 19:14:57 GMT

Hi:

I just wouldn't use a TABLE template for this type of output.

An alternative approach is shown in the code below. It is a silly form letter. In the program, the letter lines are read into an array of 10 numbered observations. There is only 1 obs in the WORK.THELETTER dataset and then that dataset is joined with 4 rows from SASHELP.CLASS. Finally, some data manipulation is done to add the appropriate information into the ln1-ln10 variables and those separate variables are all concatenated into 1 huge variable called BIGLETTER. I put the ##@## strings into the letter so that it would be easy to spot places in the letter where my logic for building the final letter text wasn't working. But you could just as easily have used INDEX and SUBSTR logic to replace those strings with variable values.

The final PROC REPORT creates 1 page of output for every NAME using BREAK processing. So you will get a multi-page RTF file -- with 1 page for every name. Sort of a kludgey approach, but I actually got the idea from an old mainframe program written in COBOL.

cynthia

****the code;

data theletter(keep=ln1-ln10);
retain ln1-ln10;
length lline $80 ln1-ln10 $300;
infile datalines dsd dlm=',';
input lettnum lline $;
array ln $300 ln1-ln10;
ln(lettnum) = lline;
** know that there are only 10 lines in the letter, so only need to output;
** at the very end;
if _n_ = 10 then output;
return;
datalines;
1, "##@##Name##@##"
2, "##@##Age##@##"
3, "~{newline 3}"
4, "Dear ##@##Name##@##"
5, "~{newline 2}"
6, "The purpose of this letter is to ##@##Purpose##@##"
7, "To redeem this offer, have your Mom or Dad call 1-800-driveNOW! ~{newline 2}"
8, "Sincerely,"
9, "~{newline 3}"
10, "A1 Driving School"
;
run;

ods listing;
proc print data=theletter;
run;
ods listing close;

** only get 15 and 16 year old students;
proc sort data=sashelp.class out=class (keep=name age);
where age ge 15;
by name;
run;

** yes, this is a cartesian product, but I want each obs to have a "fresh" copy;
** of the letter lines ln1-ln10;
proc sql;
create table together as
select *
from theletter as a,
       class as b;
quit;

ods listing;
proc print data=together;
run;
ods listing close;

data final;
** every observation will have the entire letter in one large variable string;
** based on the letter "line", add the observation specific information;
** this is not a very interesting letter. Will make 1 line a different font and color.;
length bigletter $4000;
retain ln1-ln10;
array ln $300 ln1-ln10;
set together;

** using ODS ESCAPECHAR to insert line breaks where I want them;

** and some style changes (in line 7);
do i = 1 to 10 by 1;
    if i=1 then ln(i) = catt(left(name),'~{newline 1}');
    if i = 2 then ln(i)= catt(age,' Some Str~{newline 1}','city ST 11111');
    if i = 4 then ln(i) = cat(scan(ln(i),1,'#'),trim(name),':');
    if i = 6 then ln(i) = catx(' ',scan(ln(i),1,'#'),'offer you and your parents',
                               'a 25% discount on our MasterShift driving classes. ~{newline 2}');
    if i = 7 then ln(i) = '~{style[foreground=red font_size=12pt font_weight=bold]'||trim(ln(i))||'}';
bigletter = trim(bigletter)||ln(i);
end;
run;

ods listing close;
ods escapechar = '~';
title 'A1 Driving School';
title2 'Driving Lessons For Teenagers';
title3 ' ';
footnote 'WE LOOK FORWARD TO TEACHING YOUR TEENAGER HOW TO DRIVE!';

options nodate nonumber orientation=portrait;

ods rtf file='c:\temp\letters.rtf' style=journal;

** style journal turns off all interior table lines, and, since bigletter;
** contains the WHOLE letter, with my line breaks, I am essentially creating;
** a one-cell report to contain the whole letter ;
** since the column headers are also turned off;
proc report data=final nowd noheader
       style(report)={rules=none frame=void cellspacing=0 just=l};
    column name bigletter;
    define name / order noprint;
    define bigletter / display;
    break after name / page;
run;
ods rtf close;
title; footnote;

Getting extra row when creating a table using RTF

supp — Tue, 25 Oct 2011 22:56:26 GMT

Thanks again Cynthia! With some tweaking of your program I was able to run it (I had to replace ~newline with ~n). I think with the concepts demonstrated in your program and the paper Tom linked I can achieve what I originally set out to accomplish.