 |
SAS Helpful Hints
Proc Tabulate
Proc tabulate is a very useful procedure, but personally, I do not
like the line breaks SAS puts between the data elements. Our solution
was to write a program that would read SAS tabulate output, and insert
nice line and page breaks. Our tabulate conversion program can be
cut and paste from this web page.
Change This:
Into This!
Usage Example:
%include '/Library/TabulateLib.sas';
%CleanTable(table.lst,newtable.lst);
TabulateLib.sas:
/*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}
Biostat Tabulate Format Conversion Version 2.0
This set of macros inserts nice line and page
breaks into a
Proc Tabulate with NOSEPS and ps=32767(max)
Arthur Watts 6/5/2002
{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*}{*/
%Macro CleanTable(file1,file2);
Filename ina "&file1"; *old file name;
Filename outa "&file2"; *new file name;
*Windows: Max lines per page=46 for font=9, 51 for
font=8;
*Solaris: Max lines per page=58;
%Let maxlines=51;
%Let windows=‡ƒŠŒˆ‹‚;
%Let solaris=|---+-|;
%Let specialchars=&windows;
Data work.mdevice;
If Index(Sysget('path'),'\')>0 Then Do;
Call Symput('specialchars','‡ƒŠŒˆ‹‚');
Call Symput('maxlines','51');
End; Else Do;
Call Symput('specialchars','|---+-|');
Call Symput('maxlines','58');
End;
Run;
*Find proper indents and save them in macro variables;
Data work.mAA;
Infile ina N=1 Missover Delimiter='~' End=eof;
Length aline $ 200 ztitle $ 200;
linenum=0; *Line number;
Input aline;
Do While (Substr(Left(aline),1,2)^=Substr("&specialchars",1,2));
*Save title/header lines;
linenum=linenum+1;
/*If line ends with
a page number or date, right justify, otherwise center;*/
If Length(aline)>=10
Then Do;
If
Substr(aline,Length(aline)-1,2)=' 1' and Substr(aline,Length(aline)-9,1)=','
Then Do;
*Found
page number and date on same line;
ztitle=Trim(Left(Substr(aline,1,Length(aline)-39)));
indent=Length(aline)-Length(ztitle)+1;
width=indent+Length(aline);
End;
Else
If Substr(aline,Length(aline)-1,2)=' 1' and Substr(aline,Length(aline)-9,1)^=','
Then Do;
*Found
page number without date on same line;
ztitle=Trim(Left(Substr(aline,1,Length(aline)-1)));
indent=Length(aline)-Length(ztitle)+1;
width=indent+Length(aline);
End;
Else
If Substr(aline,Length(aline)-5,1)=',' Then Do;
*Found
a date without page number(not line 1);
If
Length(aline)>=35 Then Do; *Has title;
ztitle=Trim(Left(Substr(aline,1,Length(aline)-35)));
indent=Length(aline)-Length(ztitle);
End;
Else indent=width-Length(aline); *No title;
End;
Else
indent=Int((width-Length(aline))/2.0);
End;
Select (linenum);
When
(1) Call Symput('indnt1',indent);
When
(2) Call Symput('indnt2',indent);
When
(3) Call Symput('indnt3',indent);
When
(4) Call Symput('indnt4',indent);
When
(5) Call Symput('indnt5',indent);
When
(6) Call Symput('indnt6',indent);
When
(7) Call Symput('indnt7',indent);
When
(8) Call Symput('indnt8',indent);
When
(9) Call Symput('indnt9',indent);
When
(10) Call Symput('indnt10',indent);
When
(11) Call Symput('indnt11',indent);
When
(12) Call Symput('indnt12',indent);
When
(13) Call Symput('indnt13',indent);
When
(14) Call Symput('indnt14',indent);
When
(15) Call Symput('indnt15',indent);
When
(16) Call Symput('indnt16',indent);
When
(17) Call Symput('indnt17',indent);
When
(18) Call Symput('indnt18',indent);
When
(19) Call Symput('indnt19',indent);
When
(20) Call Symput('indnt20',indent);
End;
Call Symput('indnt',indent);
Input aline;
End;
Stop;
Run;
*Create a new tabulate output!;
Data work.mAB;
Infile ina N=1 Missover Delimiter='~' End=eof;
File outa;
Array titles{20} $200;
Array bys{8} $ 50;
Length aline $ 200 break $ 200 break2 $ 200
bottom $ 200 top $ 200 spaces $ 60 ztitle $ 200;
titlnum=0; *Number of title/header lines;
bynum=0; *Number of by lines;
maxbynum=1; *Longest list of by lines;
newby=0; *Start new by list;
count=0; *Should we count by lines?;
linenum=0; *Line number;
depth=99; *Indent to label inside table;
blocksize=0; *Number of lines between breaks;
bs=0; *Temp/growing blocksize;
pagenum=1; *Page number;
newpage=0; *New page flag, to reset by variables;
spaces=' ';
Input aline;
Do While (Substr(Left(aline),1,2)^=Substr("&specialchars",1,2));
*Save title/header
lines;
linenum=linenum+1;
/*If line ends with
a page number or date, right justify, otherwise center;*/
If Length(aline)>=10
Then Do;
If
Substr(aline,Length(aline)-1,2)=' 1' and Substr(aline,Length(aline)-9,1)=','
Then Do;
*Found
page number and date on same line;
ztitle=Trim(Left(Substr(aline,1,Length(aline)-39)));
indent=Length(aline)-Length(ztitle)+1;
width=indent+Length(aline);
aline=Substr(aline,1,Length(aline)-4);
top=Trim(aline)||'
'||Compress(pagenum);
titleindent=5;
*Space between date and page number;
End;
Else
If Substr(aline,Length(aline)-1,2)=' 1' and Substr(aline,Length(aline)-9,1)^=','
Then Do;
*Found
page number without date on same line;
ztitle=Trim(Left(Substr(aline,1,Length(aline)-1)));
indent=Length(aline)-Length(ztitle)+1;
width=indent+Length(aline);
aline=Substr(aline,1,Length(aline)-4);
top=Trim(aline)||Substr(spaces,1,indent-2)||Compress(pagenum);
titleindent=indent;
*Space between title and page number;
End;
Else
If Substr(aline,Length(aline)-5,1)=',' Then Do;
*Found
a date without page number(not line 1);
If
Length(aline)>=35 Then Do; *Has title;
ztitle=Trim(Left(Substr(aline,1,Length(aline)-35)));
indent=Length(aline)-Length(ztitle);
End;
Else indent=width-Length(aline); *No title;
End;
Else
indent=width-Int(Length(aline)/2.0);
End;
titlnum=titlnum+1;
titles{titlnum}=aline;
Select (linenum);
When
(1) Put @&indnt1 top $;
When
(2) Put @&indnt2 aline $;
When
(3) Put @&indnt3 aline $;
When
(4) Put @&indnt4 aline $;
When
(5) Put @&indnt5 aline $;
When
(6) Put @&indnt6 aline $;
When
(7) Put @&indnt7 aline $;
When
(8) Put @&indnt8 aline $;
When
(9) Put @&indnt9 aline $;
When
(10) Put @&indnt10 aline $;
When
(11) Put @&indnt11 aline $;
When
(12) Put @&indnt12 aline $;
When
(13) Put @&indnt13 aline $;
When
(14) Put @&indnt14 aline $;
When
(15) Put @&indnt15 aline $;
When
(16) Put @&indnt16 aline $;
When
(17) Put @&indnt17 aline $;
When
(18) Put @&indnt18 aline $;
When
(19) Put @&indnt19 aline $;
When
(20) Put @&indnt20 aline $;
End;
Input aline;
End;
break=aline; *Primary break between blocks;
*Bottom of table/page:;
bottom=Substr("&specialchars",3,1)||Substr(break,2,Length(break)-2)||Substr("&specialchars",4,1);
plus=Index(bottom,Substr("&specialchars",5,1));
Do While (plus>0); *Replace + with inverted
Ts;
bottom=Substr(bottom,1,plus-1)||Substr("&specialchars",6,1)||Substr(bottom,plus+1,Length(bottom)-plus);
plus=Index(bottom,Substr("&specialchars",5,1));
End;
linenum=linenum+1;
Do Until (eof);
Input aline;
* Skip any page breaks;
If (Substr(aline,1,1)=Substr("&specialchars",3,1)
And Not(eof)) Then Do;
Do
Until (Substr(Left(aline),1,2)=Substr("&specialchars",1,2)
or eof);
Input
aline;
End;
Input
aline;
End;
*Check depth or indent
inside table;
l=Length(aline);
l2=Length(Left(Substr(aline,2,l-1)));
newdepth=l-l2-1;
If newdepth<depth
Then Do; count=1; cutdepth=newdepth; End;
If newdepth>depth
Then count=0;
If newdepth<depth
Or (0<bynum<=maxbynum and newdepth=depth) Or (0<bynum<maxbynum)
Then Do;
*Save
by variables, may be several lines;
If
newdepth<depth Then Do; *New by items found;
bynum=0;
If newdepth>0 Then newby=0;
End;
If
newdepth=0 Then newby=1; *New by list found;
bynum=bynum+1;
If
(newdepth^=depth and bynum>maxbynum) Then newby=0; *Completed saving
by lines;
Else
Do;
rtsend=1+Index(Substr(aline,2,Length(aline)-1),Substr("&specialchars",7,1));
If
rtsend>1 Then Do; *Not EOF;
If
cutdepth=0 or newby>0 Then bys{bynum}=Substr(aline,2,rtsend-2);
Else
bys{bynum}=Substr(bys{bynum},1,cutdepth)||Substr(aline,cutdepth+2,rtsend-cutdepth+1);
If
count>0 Then maxbynum=max(maxbynum,bynum);
End;
End;
End; Else Do; bynum=0;
newby=0; End;
If newdepth<depth
Then Do; *End of block, insert a break;
blocksize=Max(blocksize,bs);
bs=0;
If
linenum+blocksize+1<&maxlines Then Do; *Not a new page;
linenum=linenum+1;
If
newdepth>0 Then Do; *Insert a break, Update by variables;
*Insert
by variables if this is the beginning of a new page;
If
1<=newpage<=maxbynum Then Do;
If
newdepth>0 Then
break2=Substr("&specialchars",7,1)||Substr(bys{newpage},1,newdepth)||Substr(break,newdepth+2,l2);
newpage=newpage+1;
If
newpage>maxbynum Then newpage=0;
End;
Else break2=Substr("&specialchars",7,1)||Substr(spaces,1,newdepth)||Substr(break,newdepth+2,l2);
Put
@&indnt break2 $;
End;
Else
If rtsend>1 Then Put @&indnt break $; *If not EOF;
End;
Else
If Not(eof) Then Do; *New Page;
newpage=1;
Put
@&indnt bottom $;
Put
' ' @; *Page Break;
pagenum=pagenum+1;
top=Trim(titles{1})||Substr(spaces,1,titleindent-4)||'
'||Compress(pagenum);
If
pagenum>9 Then top=Trim(titles{1})||Substr(spaces,1,titleindent-4)||'
'||Compress(pagenum);
If
pagenum>99 Then top=Trim(titles{1})||Substr(spaces,1,titleindent-4)||Compress(pagenum);
If
pagenum>999 Then top=Trim(titles{1})||Compress(pagenum);
Put
@&indnt1 top $;
Do
i=2 To titlnum;
Select
(i);
When
(2) Put @&indnt2 titles{i} $;
When
(3) Put @&indnt3 titles{i} $;
When
(4) Put @&indnt4 titles{i} $;
When
(5) Put @&indnt5 titles{i} $;
When
(6) Put @&indnt6 titles{i} $;
When
(7) Put @&indnt7 titles{i} $;
When
(8) Put @&indnt8 titles{i} $;
When
(9) Put @&indnt9 titles{i} $;
When
(10) Put @&indnt10 titles{i} $;
When
(11) Put @&indnt11 titles{i} $;
When
(12) Put @&indnt12 titles{i} $;
When
(13) Put @&indnt13 titles{i} $;
When
(14) Put @&indnt14 titles{i} $;
When
(15) Put @&indnt15 titles{i} $;
When
(16) Put @&indnt16 titles{i} $;
When
(17) Put @&indnt17 titles{i} $;
When
(18) Put @&indnt18 titles{i} $;
When
(19) Put @&indnt19 titles{i} $;
When
(20) Put @&indnt20 titles{i} $;
End;
End;
linenum=titlnum+1;
Put
@&indnt break $;
End;
End;
*Insert by variables
if this is the beginning of a new page;
If 1<=newpage<=maxbynum
Then Do;
If
newdepth>0 Then
aline=Substr("&specialchars",7,1)||Substr(bys{newpage},1,newdepth-1)||Substr(aline,newdepth+1,l2+1);
newpage=newpage+1;
If
newpage>maxbynum Then newpage=0;
End;
linenum=linenum+1;
bs=bs+1;
depth=l-1-l2;
Put @&indnt aline
$;
End;
Run;
Proc Datasets Library=work Memtype=data;
Delete mdevice mAA mAB;
Run;
%Mend CleanTable;
%Macro indentdef;
%Do i=1 %To 20;
%Global indnt&i;
%Let indnt&i=1;
%End;
%Mend indentdef;
%indentdef;
Please send your comments and suggestions about this web page to A. Watts
(watts@bst.rochester.edu)
|
