topic Re: Excess Blank Lines in Proc Tabulate with ODS in ODS and Base Reporting

Excess Blank Lines in Proc Tabulate with ODS

deleted_user — Mon, 10 Jul 2006 15:49:48 GMT

I'm making a basic PDF report with Proc Tabulate. From left to right, I have a main grouping category, a common set of subgroups for each category, followed by a few columns under which all my counts and percentages are listed. Those columns have titles, but my category and subcategory columns do not. (Sorry if this is confusing...it's hard to recreate the actual report in text format.)

Anyway, starting with the second main grouping category, there's an extra blank line at the top of the subgroup column within each main group. It only shows up on the PDF version of the report, not on the basic SAS output--probably because I used the row=float option. However, I know that option is not supposed to actually work in ODS output.

Any ideas of how I can eliminate this bothersome extra blank line at the start of every group in my PDF output? I'm using a custom style, which is similar to Printer but with some edited fonts and text sizes.

Thanks!

Re: Excess Blank Lines in Proc Tabulate with ODS

David_SAS — Mon, 10 Jul 2006 16:45:30 GMT

I believe you have encountered a known problem with TABULATE and ODS PRINTER. This problem has been fixed for the 9.2 release of SAS.

I don't have a suggestion for a workaround, sorry.

-- David Kelley, SAS

Re: Excess Blank Lines in Proc Tabulate with ODS

Cynthia_sas — Mon, 10 Jul 2006 17:40:44 GMT

Elayne:
Here's something that may be feasible for you -- but not using TABULATE directly. If you create an output dataset using PROC TABULATE and then massage the _TYPE_ values a bit, you can use PROC REPORT to print out something that looks very close to the original TABULATE output. The sample program below uses SASHELP.PRDSALE and was designed with labels that fit a previous student's report. (So, for example, COUNTRY was not really being used as COUNTRY, but was formatted to display as delinquency categories to help the student visualize the changes for her program.)
cynthia
[pre]
** make a format to display country like a;
** delinquency category;
proc format;
value $bktfmt (notsorted)
'CANADA' = 'Current'
'GERMANY' = '30'
'U.S.A.' = '60';
run;
ods listing close;
ods pdf file='redo_tab.pdf';

proc tabulate data=sashelp.prdsale
out=work.prdout;
title 'with row=float';
where put(month, monname3.) in ('Jan', 'Feb', 'Mar');
format month monname3.;
format Country $bktfmt.;
class month;
classlev month / s={background=cxFFFF99
font_face=Arial
foreground=cx3366FF
font_size=8pt just=C};
class country /preloadfmt order=data;
class prodtype ;
var actual;
table prodtype=''*(month='')
all="Total"*(month=''),
country=''*(
n={label="#'s"}
actual={label="$'s"}*sum=''*f=dollar12.)
all='Total Servicing Portfolio'*(n={label="#'s"}
actual={label="$'s"}*sum=''*f=dollar12.)
/ printmiss misstext="0"
box='Report Data'
row=float ;
run;

proc print data=work.prdout;
title 'output from tabulate';
run;

** massage the data to get the _type_ in the;
** right rows and columns for proc report;
** also create a var called CNT from N created by TAB;
data prdout2;
set work.prdout;
if _type_ = 101 then country = 'zzzz';
else if _type_ = 110
then prodtype = 'Total';
else if _type_ = 100 then do;
country = 'zzzz';
prodtype='Total';
end;
Cnt = N;
run;

** make a format for proc report;
proc format;
value $repfmt (notsorted)
'CANADA' = 'Current'
'GERMANY' = '30'
'U.S.A.' = '60'
'zzzz' = 'Total Servicing Portfolio';
run;

proc report data=prdout2 nowd;
title 'Using Proc Report on Tabulate dataset';
column ('Report Data' prodtype month)
country,(cnt Actual_Sum);
define prodtype / group 'Type' style(column)=Header;
define month /group order=data 'Mon'
style(column)={background=cxFFFF99
font_face=Arial
foreground=cx3366FF
font_size=8pt just=C};
define country /across ' ' order=data f=$repfmt.;
define cnt /sum f=comma6. "#'s";
define Actual_Sum / sum f=dollar14.0 "$'s";
run;
ods pdf close;
ods listing;

[/pre]

Re: Excess Blank Lines in Proc Tabulate with ODS

deleted_user — Wed, 12 Jul 2006 14:34:35 GMT

David: So, when does 9.2 come out? 😉

Cynthia: Thanks, your solution worked. I often use a similar solution using proc means on the original dataset, but it requires a few steps thereafter to reach a dataset that has proper numerators, denominators, and calculated percentages. Your solution felt a little cumbersome at first (I had to specify what to do with eight different _TYPE_ values in an if-then) but it did achieve the same end product in fewer steps, programatically.

Re: Excess Blank Lines in Proc Tabulate with ODS

David_SAS — Wed, 12 Jul 2006 16:48:49 GMT

SAS 9.2 is scheduled to ship in the first quarter of 2007.

-- David Kelley, SAS

Re: Excess Blank Lines in Proc Tabulate with ODS

deleted_user — Tue, 18 Jul 2006 15:52:24 GMT

Try to put indent=0 into the end of our table statement.
Like this /BOX='Xxxxxxxx' INDENT=0; That works for me.

Re: Excess Blank Lines in Proc Tabulate with ODS

deleted_user — Fri, 21 Jul 2006 19:25:26 GMT

You are absolutely right. I only tried it on one instance but it worked just fine.

Kudos and thanks!