topic Re: Blanks between tables with Proc Report and ODS RTF in SAS Programming

Blanks between tables with Proc Report and ODS RTF

Garpe — Wed, 04 May 2022 14:43:04 GMT

Hello,

I would like to output several tables with no blank between the different tables, thanks to ODS RTF.

The result I get :

	N	%
Variable 1
Modality 1
Modality 2

BLANK


Variable 2
Modality 1
Modality 2

The result I would like :

	N	%
Variable 1
Modality 1
Modality 2
Variable 2
Modality 1
Modality 2

Here is the code I use :

OPTIONS ORIENTATION=LANDSCAPE CENTER NODATE NONUMBER ; TITLE ; FOOTNOTE ; 
ODS RTF body="myfile" STARTPAGE=NO BODYTITLE STYLE=styles.journal;
ODS ESCAPECHAR='~' ; ODS NOPROCTITLE ; ods rtf text="~{STYLE[just=c vjust=c outputheight=0.5cm outputwidth=100% font_weight=bold font_size=10pt FONT_FACE ='couriernew, courier']}";

PROC REPORT data = mytable......
PROC REPORT data = mytable......
PROC REPORT data = mytable......
ODS RTF CLOSE ;

The only solution I found is a macro from the article "Constructing stack tables with Proc Report and ODS RTF" written by Lei Zhang.

%Macro glue(in=, out=);
%local QueueSize;
%let QueueSize=5;
filename infile "&in";
filename outfile "&out";
data _TMPDSN;
 length rtfcode $32000;
 infile infile; * original rtf tables created by ODS;
 input;
rtfcode=_infile_;
length=length(trim(rtfcode));
run;
data _null_;
 file outfile; * rtf stack table with no gaps between child tables;
set _TMPDSN end=last;
array queue{&QueueSize} $32000 _temporary_;
retain queue;
 retain count 0;
count = count + 1;
queue[count] = rtfcode;
if count = &QueueSize then do;
 found = 0;
 if (queue{1} = '\pard{\par}' and 
 queue{2} = '{\par}{\pard\plain\qc{' and 
 queue{3} = '}\par}{\par}' and
 queue{4} = ' ') then do;
 if (compress(translate(queue{&QueueSize},"", "0123456789")) = 
'\sect\sectd\linex\endnhere\sbknone\headery\footery\marglsxn\margrsxn\margtsxn\margbsx
n') then found=1;
 end;
 if found then do;
 count=0;
 end; else do;
 put queue{1};
 do i = 2 to &QueueSize;
 queue[i-1]=queue[i];
 end;
 count = count -1;
 end;
end;
if last then do;
do i = 1 to count;
 put queue{i};
end;
end;
run;
%Mend glue;

It takes the RTF file as a parameter and returns a new file with stuck tables.

It works perfectly for a document with portrait orientation but doesn't word in landscape orientation.

Does anyone have a solution for a landscape file ?

Thank you,

Re: Blanks between tables with Proc Report and ODS RTF

Reeza — Wed, 04 May 2022 16:42:06 GMT

If your proc report is not too complicated I would suggest using the OUT=option on PROC REPORT to pipe the output to data sets, combine the data sets and then use a fourth proc report to get the desired structure.

I'm assuming you don't want the headers repeated? If you do want the headers repeated another option may be to modify the template so that there is no spaces after tables. It seems like PARKSKIP is the template option.

If you're going to do this many times I'd look into option2, if you're doing this once or twice I'd use the first option.

Re: Blanks between tables with Proc Report and ODS RTF

Cynthia_sas — Wed, 04 May 2022 17:54:21 GMT

Hi:

My tendency with this kind of issue would be to look to combine the 2 files, and then use helper variables in PROC REPORT to get the report rows in the order you want, something like this:

I had to make some fake data to do this and make a first pass through the example. So here's the first pass:

** using 2 tables with ODS RTF;

data one;
  length description $10;
  infile datalines dlm=',';
  input description cnt pct;
return;
datalines;
Modality 1, 100,.50
Modality 2, 100, .50
;
run;

data two;
  length description $10;
  infile datalines dlm=',';
  input description cnt pct;
return;
datalines;
Modality 1, 200,.50
Modality 2, 200, .50
;
run;


OPTIONS ORIENTATION=LANDSCAPE CENTER NODATE NONUMBER ; 
TITLE ; FOOTNOTE ; 
ODS RTF body="c:\temp\mult_tables.rtf" STARTPAGE=NO BODYTITLE STYLE=styles.journal;
ODS ESCAPECHAR='~' ; ODS NOPROCTITLE ; 

PROC REPORT data = one;
  column description cnt pct;
  define description / order ' ';
  define cnt / sum f=3.0 'N';
  define pct / analysis f=percent9.0 '%';
  compute before / style=Header{just=l} ;
    line 'Variable 1';
  endcomp;
run;

PROC REPORT data = two;
  column description cnt pct;
  define description / order ' ';
  define cnt / sum f=3.0 'N';
  define pct / analysis f=percent9.0 '%';
  compute before / style=Header{just=l} ;
    line 'Variable 2';
  endcomp;
run;
ODS RTF CLOSE ;

This program produced 2 separate tables in a landscape RTF file, with space between the 2 tables.

So what I did was take those 2 separate files (WORK.ONE and WORK.TWO) and combined them together to make WORK.FINAL. Then I got the output I showed by running this program:


** rather than try to remove the space after the RTF is created;
** use helper variables to combine the 2 files and use 1 PROC REPORT step;

data final;
  length grpname $15 ;
  set one(in=v1)
      two(in=v2);
  if v1 = 1 then do;
     grpnum = 1;
     grpname = 'Variable 1';
     roword=_n_;
  end;
  else if v2 = 1 then do;
     grpnum = 2;
     grpname = 'Variable 2';
     roword=_n_;
  end;
run;

OPTIONS ORIENTATION=LANDSCAPE CENTER NODATE NONUMBER ; 
TITLE ; FOOTNOTE ; 
ODS RTF body="c:\temp\use_combined_tables.rtf" STARTPAGE=NO BODYTITLE STYLE=styles.journal;
ODS ESCAPECHAR='~' ; ODS NOPROCTITLE ; 

PROC REPORT data =final;
  column grpnum grpname roword description cnt pct;
  define grpnum / order noprint;
  define grpname / order noprint;
  define roword /order noprint;
  define description / display ' ';
  define cnt / sum f=3.0 'N';
  define pct / analysis f=percent9.0 '%';
  compute before grpname/ style=Header{just=l textdecoration=underline} ;
    line grpname $15.;
  endcomp;
run;
 
ODS RTF CLOSE ;

Note that all my "helper" variables are NOPRINT items and that I used the COMPUTE BEFORE in order to insert the Variable 1 and Variable 2 above each group. I added TEXTDECORATION=UNDERLINE on those rows to make them stand out.

Cynthia

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Thu, 05 May 2022 14:26:08 GMT

Thank you for your answers ! I had already tried solution 1 but I couldn't apply it (mainly because the variables have different formats).
For option 2, I can't find PARKSKIP... I forgot to mention that I was using SAS software.

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Thu, 05 May 2022 14:36:44 GMT

Many thanks for you help !
I'm sorry, I forgot to specify that in reality I didn't have 2 tables to "stick" but a large number.
I am having two problems:
- for the data step : I use a macro, the number of tables to stick varies from time to time
- for the Proc Report step : variables have different formats (for example "1" means "Female" for variable 1 and means "Asian" for variable 2), so I think that a single Proc Report will not work here

Thank you,
G.

Re: Blanks between tables with Proc Report and ODS RTF

Reeza — Thu, 05 May 2022 14:44:58 GMT

Then you may need to do the template approach.

Is there a reason you're not using proc tabulate across a set of variables for this report?
Or a macro that process many at once into one table?
Unfortunately your current approach makes it difficult to get the output you desire.

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Thu, 05 May 2022 15:13:48 GMT

I simplified the situation but in reality I am creating many tables, of qualitative and quantitative variables, using a macro (with several Proc tabulate and Proc report). I failed to get my different variables to output through a single procedure.

Do you have any idea for th template option ?

Thanks for your time.
G.

Re: Blanks between tables with Proc Report and ODS RTF

Cynthia_sas — Thu, 05 May 2022 15:59:58 GMT

Hi:

You can have a user-defined format for different variables where the value 1 means different things for different variables. Here's an example in this paper on page 8: https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/173-2008.pdf the format that was used for those values are shown below:

You can see where these values were used in the report on page 8. The programs that go with the paper can be found here: https://support.sas.com/rnd/papers/#SGF2008 and you just need to scroll down to the section for this paper (Creating Complex Reports) so you can download the zip file of programs.

The key to doing the kind of report that needs the structure you show is that you need to apply the format before you run the PROC REPORT. On my example, each of those sections, for Age, for Gender, for LVEF, etc was either a separate PROC FREQ or PROC MEANS step and then the output from those procedures used the formats to make the labelled values before combining all the separate tables together so that one PROC REPORT could be used for the final report:

There is a TABLE template version of the demographic report as the last example in the paper.

Cynthia

Re: Blanks between tables with Proc Report and ODS RTF

Reeza — Thu, 05 May 2022 16:06:28 GMT

Can you use this macro instead?

https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-TABLEN/ta-p/634030

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Fri, 06 May 2022 09:18:29 GMT

I'm sorry, I'm quite notice and I don't understand this explanation. I already apply my formats before the final Proc Report thanks to the following code:

PROC FORMAT;
value myvarone
0 = "F"
1 = "M";
value myvartwo
0 = "Red" 
1 = "Blue";
run;

DATA mytable;
input variable1 variable2;
datalines;
0 1
0 1
0 0
0 0
1 1;
;
run;

PROC TABULATE data = mytable; 
class	variable1 / PRELOADFMT;
table (variable1), (N PCTN) / PRINTMISS; 
ods output table=DIS_VARIABLE1(drop = _TYPE_ _PAGE_ _TABLE_); 
format variable1  myvarone.;
run;

PROC TABULATEdata = mytable; 
class	variable2 / PRELOADFMT;
table (variable2), (N PCTN) / PRINTMISS; 
ods output table=DIS_VARIABLE2 (drop = _TYPE_ _PAGE_ _TABLE_); 
format variable2  myvartwo.;
run;

DATA DIS_VARIABLE1;
set DIS_VARIABLE1;
grpnum = 1;
grpname = "Variable 1";
roword = _n_;
VAR = variable1;
run;

DATA DIS_VARIABLE2;
set DIS_VARIABLE2;
grpnum = 2;
grpname = "Variable 2";
roword = _n_;
VAR = variable2;
run; 

DATA final;
set DIS_VARIABLE1 DIS_VARIABLE2;
run;

PROC REPORT data = final NOWD
STYLE(lines) = [JUST = LEFT]/*justification du nom de la variable à gauche*/
STYLE(report) = [FRAME = VOID];/*enlever les bordures*/
/*liste des variables*/
column GRPNUM grpname roword VAR (N PctN_0);
/*définition des rôles des var*/
define GRPNUM / order noprint;
define grpname / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define roword / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define VAR / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define N / analysis  style(column)=[cellwidth=21mm];
define PctN_0 / analysis format=comma9.1 style(column)=[cellwidth=21mm];
compute before grpname/ style=Header{just=l textdecoration=underline} ;
    line grpname $15.;
endcomp;
run;

However, in the FINAL table the formats no longer exist, so in my final Proc Report this is going to be a problem...

Thank you,

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Fri, 06 May 2022 09:21:46 GMT

I did not know this macro, it seems to be great ! However, I have output constraints (especially for numeric variables) so I have to use my own program...

G.

Re: Blanks between tables with Proc Report and ODS RTF

Reeza — Fri, 06 May 2022 15:28:31 GMT

As initially mentioned you have two (maybe three) options.

1. Combine your data sets (input or output datasets). If you're using proc tabulate/report - both procs have an OUT option on the statement that allows you to combine the OUTPUT (Not input data) and then display them.

2. Modify the template using PROC TEMPLATE which is complicated, but the style item required to be modified is PARKSKIP. Personally, I'd avoid this and modify my code and logic as it's easier to understand and maintain.

Unfortunately SAS doesn't provide any other methods to modify the spacing that I'm aware of at this time and if anyone knew, it would likely be Cynthia.

User papers written on the topic are here:

https://www.lexjansen.com/search/searchresults.php?q=creating%20a%20style%20template

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsproc/p0cgtsocrm3ei3n1uqw9v4mbswkh.htm

Also, not sure what you'd do if you wanted the tables without spaces in one part of the report and then not the other, except for changing styles...

Or you could look into ODS DOCUMENT but that requires way more control over where everything goes.

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Fri, 06 May 2022 15:55:48 GMT

Under your advice, I am going to abandon the idea of modifying my template !
For option 1, it seems to me that is what I do. But I'm facing a problem with my format : to display all my variables in a single table I use at the end a Proc report. I gather all the modalities in the same variable VAR (see code below). However, these modalities in VAR have different formats : it seems impossible to me to apply several format to a single variable... (that's why I originally decided to output the tables separately)

PROC FORMAT;
value myvarone
0 = "F"
1 = "M";
value myvartwo
0 = "Red" 
1 = "Blue";
run;

DATA mytable;
input variable1 variable2;
datalines;
0 1
0 1
0 0
0 0
1 1;
;
run;

PROC TABULATE data = mytable; 
class	variable1 / PRELOADFMT;
table (variable1), (N PCTN) / PRINTMISS; 
ods output table=DIS_VARIABLE1(drop = _TYPE_ _PAGE_ _TABLE_); 
format variable1  myvarone.;
run;

PROC TABULATEdata = mytable; 
class	variable2 / PRELOADFMT;
table (variable2), (N PCTN) / PRINTMISS; 
ods output table=DIS_VARIABLE2 (drop = _TYPE_ _PAGE_ _TABLE_); 
format variable2  myvartwo.;
run;

DATA DIS_VARIABLE1;
set DIS_VARIABLE1;
grpnum = 1;
grpname = "Variable 1";
roword = _n_;
VAR = variable1;
run;

DATA DIS_VARIABLE2;
set DIS_VARIABLE2;
grpnum = 2;
grpname = "Variable 2";
roword = _n_;
VAR = variable2;
run; 

DATA final;
set DIS_VARIABLE1 DIS_VARIABLE2;
run;

PROC REPORT data = final NOWD
STYLE(lines) = [JUST = LEFT]/*justification du nom de la variable à gauche*/
STYLE(report) = [FRAME = VOID];/*enlever les bordures*/
/*liste des variables*/
column GRPNUM grpname roword VAR (N PctN_0);
/*définition des rôles des var*/
define GRPNUM / order noprint;
define grpname / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define roword / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define VAR / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define N / analysis  style(column)=[cellwidth=21mm];
define PctN_0 / analysis format=comma9.1 style(column)=[cellwidth=21mm];
compute before grpname/ style=Header{just=l textdecoration=underline} ;
    line grpname $15.;
endcomp;
run;

Many thanks,

Re: Blanks between tables with Proc Report and ODS RTF

Cynthia_sas — Fri, 06 May 2022 16:07:54 GMT

Hi:

I think your program was nearly there. I tweaked the DATA step where you created the helper variables to use the User-defined formats, as shown below:

And that allowed me to take the NOPRINT off of the VAR item on the final PROC REPORT. Is this closer to what you wanted??

Cynthia

Re: Blanks between tables with Proc Report and ODS RTF

Reeza — Fri, 06 May 2022 16:27:13 GMT

@Garpe wrote:

PROC FORMAT;
value myvarone
0 = "F"
1 = "M";
value myvartwo
0 = "Red" 
1 = "Blue";
run;

DATA mytable;
input variable1 variable2;
datalines;
0 1
0 1
0 0
0 0
1 1;
;
run;

PROC TABULATE data = mytable; 
class	variable1 / PRELOADFMT;
table (variable1), (N PCTN) / PRINTMISS; 
ods output table=DIS_VARIABLE1(drop = _TYPE_ _PAGE_ _TABLE_); 
format variable1  myvarone.;
run;

PROC TABULATEdata = mytable; 
class	variable2 / PRELOADFMT;
table (variable2), (N PCTN) / PRINTMISS; 
ods output table=DIS_VARIABLE2 (drop = _TYPE_ _PAGE_ _TABLE_); 
format variable2  myvartwo.;
run;

DATA DIS_VARIABLE1;
set DIS_VARIABLE1;
grpnum = 1;
grpname = "Variable 1";
roword = _n_;
VAR = variable1;
run;

DATA DIS_VARIABLE2;
set DIS_VARIABLE2;
grpnum = 2;
grpname = "Variable 2";
roword = _n_;
VAR = variable2;
run; 

DATA final;
set DIS_VARIABLE1 DIS_VARIABLE2;
run;

PROC REPORT data = final NOWD
STYLE(lines) = [JUST = LEFT]/*justification du nom de la variable à gauche*/
STYLE(report) = [FRAME = VOID];/*enlever les bordures*/
/*liste des variables*/
column GRPNUM grpname roword VAR (N PctN_0);
/*définition des rôles des var*/
define GRPNUM / order noprint;
define grpname / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define roword / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define VAR / order noprint order = internal style(column)=[cellwidth=70mm JUST=LEFT leftmargin=10mm];
define N / analysis  style(column)=[cellwidth=21mm];
define PctN_0 / analysis format=comma9.1 style(column)=[cellwidth=21mm];
compute before grpname/ style=Header{just=l textdecoration=underline} ;
    line grpname $15.;
endcomp;
run;

Many thanks,

PROC FORMAT;
value myvarone
0 = "F"
1 = "M";
value myvartwo
0 = "Red" 
1 = "Blue";
run;

DATA mytable;
input variable1 variable2;
datalines;
0 1
0 1
0 0
0 0
1 1
;;;;
run;

ods table onewayfreqs=temp;
proc freq data=mytable;
	table variable1 variable2;
	format variable1 myvarone. variable2 myvartwo.;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
proc report data=want nowd style(lines) = [JUST=LEFT] style(report) = [frame=void];
column variable variable_value frequency percent;
define variable / order noprint;
define variable_value/ '' display;
define frequency / 'N' analysis;
define percent / 'PctN_0' analysis format=comma9.1;
compute before variable / style=header{just=L textdecoration=underline};
    line variable $32.;
endcomp;
run;

You can fix the style to what you need.

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Wed, 11 May 2022 08:09:32 GMT

Thank you for your time. It works. However, I had other problems with this solution because I have quantitative and qualitative variables in the same table, so I will create several tables and then modify my template with the PARSKIP option as suggested by @Reeza.
Have a good day.
G.

Re: Blanks between tables with Proc Report and ODS RTF

Garpe — Wed, 11 May 2022 08:11:57 GMT

Many thanks ! I managed to use the PARSKIP option of the Proc TEMPLATE. I will use this solution.

Good afternoon.