The SAS Output Delivery System and reporting techniques

Excess Blank Lines in Proc Tabulate with ODS

Reply
N/A
Posts: 0

Excess Blank Lines in Proc Tabulate with ODS

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!
SAS Employee
Posts: 174

Re: Excess Blank Lines in Proc Tabulate with ODS

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
SAS Super FREQ
Posts: 8,739

Re: Excess Blank Lines in Proc Tabulate with ODS

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]
N/A
Posts: 0

Re: Excess Blank Lines in Proc Tabulate with ODS

David: So, when does 9.2 come out? Smiley Wink

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.
SAS Employee
Posts: 174

Re: Excess Blank Lines in Proc Tabulate with ODS

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

-- David Kelley, SAS
N/A
Posts: 0

Re: Excess Blank Lines in Proc Tabulate with ODS

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

Re: Excess Blank Lines in Proc Tabulate with ODS

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

Kudos and thanks!
Ask a Question
Discussion stats
  • 6 replies
  • 208 views
  • 0 likes
  • 3 in conversation