The SAS Output Delivery System and reporting techniques

create same size table (same size colums) in proc print using by variable?

Reply
Frequent Contributor
Frequent Contributor
Posts: 81

create same size table (same size colums) in proc print using by variable?

Hi
I am trying to create a pdf file using proc print with by variable. But the tables that are created with by variable has different sizes(columns sizes are different). How do I make all columns and tables with same size with by variable.

for example. If any one excute the following code. it creates two tables in PDF format, but the first table's column size is little bigger than the second table's column. How do I make both of them with the same size.

Q2> Also it print the page number at the top right corner of the pdf file. How do I stop this?

ods listing close;
data temp;
input x $10. y $12.;
cards;
ssssssssss uuuuuuuuu
ssssssssss yyyyyyyyy
ssssssssss yyyyyyyyy
wwwwwwwwww t
wwwwwwwwww i
wwwwwwwwww e
;
run;
ods listing close;
ods pdf file='c:\test.pdf' uniform;
proc print data= temp;
by x;
run;
ods pdf close;
ods listing;


Thanks for your help in advance.


Thanks

Inp
SAS Super FREQ
Posts: 8,864

Re: create same size table (same size colums) in proc print using by variable?

Hi:
The options NODATE NONUMBER will suppress the date and page number in the top right of the output. If you use the CELLWIDTH option, you can specify a width for var Y that will ensure both columns are the same size. By default, every BY group is treated/handled separately by most SAS procedures -- so an explicit CELLWIDTH is the way around your issue.

cynthia
[pre]
ods listing close;
options nodate nonumber;
ods pdf file='c:\temp\cellwidth_nopage.pdf' uniform;
proc print data= temp;
by x;
var y / style(data)={cellwidth=1.25in};
run;
ods pdf close;
ods listing;
[/pre]
N/A
Posts: 0

Re: create same size table (same size colums) in proc print using by variable?

Posted in reply to Cynthia_sas
Cynthia

would there be some kind of automation possible?
Are we able to calculate "required cell-width" from column format width and font size?
If there is an adequate approximation, I think it could be generated through PROC SQL into macro variables for each var providing its cellwidth style values.
The effect might be similar to the UNIFORM option of proc print.

PeterC
SAS Super FREQ
Posts: 8,864

Re: create same size table (same size colums) in proc print using by variable?

Posted in reply to deleted_user
Hi, Peter:
ODS PDF takes into account font size, font face, cell padding, cell spacing, border width, (from the style template) and number of characters in the column, if the column is text, number of total characters, including spaces and punctuation in the string. (Column headers are also taken into account and whether there are any spanning headers, because a skinny column could have a fat header...and vice versa)

If you -could- automate it, you would essentially be doing what PDF is already doing. And you'd have to do that for EVERY by group to come up with the max for all the by groups and then use that value for the cell width. My approach is way less scientific -- I picked a number that looked approximately OK, ran the program once, fiddled with the number a bit more and then that was the final number I used.

Numbers can be specified as percents, as well as in absolute inches or cm. Also, I've discovered that if I try to make the size too small, if my size conflicts with what PDF wants to set, it will not use the smaller size -- because of the algorithm.

When you say "required" cell-width, I have to ask required by whom? ODS PDF -did- calculate the required cell width for the separate by groups in the original program that was posted. The results were apparently unsatisfactory -- since each BY group is treated as a whole, the first table had a different cell width for var Y than the second table. ODS PDF did calculate the required cell width -- as PDF operated within the rules of SAS -- but the cell width that it calculated was for each BY group.

If you notice, the original code DID have the UNIFORM option for ODS PDF. The UNIFORM option at the "ODS" level does not calculate across BY groups. However, if you tried this code with the PROC PRINT UNIFORM option, it would produce the same results as the original undesired results -- with different cell widths for each BY group.

I don't know whether this behavior (treating each BY group separately) is something that could be changed. Since there's is a way to alter the cell width that ODS PDF wants to set, I suppose you could try to automate it -- but I'd probably try something easier like figuring out the number of columns, and allocating a percent to each column first. Like if there are 5 columns, make each column a cellwidth of 20%, etc, etc.

cynthia
Frequent Contributor
Frequent Contributor
Posts: 81

Re: create same size table (same size colums) in proc print using by variable?

Posted in reply to Cynthia_sas
Hi Cynthia,
If don't hard code the cell with, it will produce the report with diffrent size.Is there any way to find the value of the cell width of the fist table before hardcoding the cell width, so that I can go back to the program and edit the cell width so the all tables will be the same size.



Thanks

Inp
N/A
Posts: 0

Re: create same size table (same size colums) in proc print using by variable?

as part of the process, you could generate the code to determine the max character width of all columns of the table, with something like[pre]%let lib= SASHELP ;
%let mem= CLASS ;
PROC SQL NOPRINT ;
select compbl( 'l_'!! name !!
'= max( l_'!! name !! ', length( compbl( vvalue( '
!! name !! ')))); ' )
into :flens separated by ' '
from dictionary.columns
where libname="&lib" and memname="&mem"
;
%put %quote(&flens);
quit;
data lens( keep= l_: ) ;
set &lib..&mem end= eof ;
&flens
retain l_: ;
if eof ;
put (L_Smiley Happy(=) ;
run ;[/pre]Notice that it returns a length of 1 for sex. You might want to balance minimising the widths with providing sufficient width for the header.
Once you have this information, you might be able to set the style cellwidth at this count in "em" units (width of the letter m).
All this work is justified, only, if you need to provide a generalised solution.
The next steps, take the max formatted widths and subject to minimum width rules you may prefer, generates the var statements for each column of the table.
To keep the generic solution as simple as manageable wil allow, ensure all table columns are relevant and in order.
Here is a test script for sashelp.class using the "em" cellwidth units[pre]ods pdf file='cellwidth_nopage.pdf' uniform;
proc print data= sashelp.class ;
var name / style(data)={cellwidth=7em };
var sex / style(data)={cellwidth=3em };
var height/ style(data)={cellwidth=5em };
var weight/ style(data)={cellwidth=6em };
run;
ods pdf close;
dm 'winexecfile cellwidth_nopage.pdf' ; [/pre]

good luck

PeterC "in em units" not "if em units"


Message was edited by: Peter_c
SAS Super FREQ
Posts: 8,864

Re: create same size table (same size colums) in proc print using by variable?

Posted in reply to deleted_user
Peter: I knew you would try to write the program! Nice approach.
cynthia
N/A
Posts: 0

Re: create same size table (same size colums) in proc print using by variable?

Posted in reply to Cynthia_sas
:-)
thankyou

those "em" units _are_ helpful
shame function vvalue() is not allowed in sql

I wonder if the op will consider generalising worth the fuss

P
N/A
Posts: 0

Re: create same size table (same size colums) in proc print using by variable?

Posted in reply to deleted_user
Cynthia

having tried the 9em width for the Y variable, it is clearly over-wide.

If there is no sas routine/function for width calculation in a chosen font, would the java object iterface offer such a solution through a data step?

It is outside of my "comfort zone" but there are all sorts of expertise watching. I hestiate to cross-post to other forums ... over to you;-)

PeterC
SAS Super FREQ
Posts: 8,864

Re: create same size table (same size colums) in proc print using by variable?

Posted in reply to deleted_user
Hi, Peter:
I don't know. I see places in this paper where width and guttering is set, but I don't see any dynamic calculations of cell width of the sort that you want.
http://support.sas.com/rnd/base/datastep/dsobject/Power_to_show_paper.pdf

Since the information coming from the style template can be overridden, I see how you can capture the override for your calculations, but I don't see how you would retrieve the default information for such calculations.

And truly, a DATA step solution for a BY group table seems like a lot of work compared to letting PROC PRINT do it with a CELLWIDTH -- even if it is a bit big. Generally, I find it is hard to automate for -every- possibility -- headers, columns, font size, font face, cell spacing, cell padding, internal width of border lines.

For reports where cosmetics are more important than anything else, I expect to do some manual "fiddling" -- there's nothing like the eye for catching places where something could be a smidge smaller or a skosh larger.

cynthia
Ask a Question
Discussion stats
  • 9 replies
  • 274 views
  • 0 likes
  • 3 in conversation