Date: Fri, 30 Sep 2005 10:01:03 -0400
Reply-To: Robert Sayre <rsayre@ACXIOM.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Robert Sayre <rsayre@ACXIOM.COM>
Subject: proc tabulate: How can I change column widths with ods markup?
Hi,
I'm creating a proc tabulate report and sending it to excel via ods. How
do I change the cellwidth? Currently the fields are wider than I would
like. I've made it 'fit_to_page' but that doesn't change the cell width.
Is there a way to get excel to 'auto set' the width, similar to when you
select the whole worksheet and then click on the edge of the cell? Or is
there a way to set each columnwidth manually in tabulate that will carry
over into excel?
My code:
proc template;
define tagset tagsets.test;
parent=tagsets.htmlcss;
define event doc;
start:
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put VALUE NL;
put "<style>" NL;
put "<!--" NL;
trigger alignstyle;
put "-->" NL;
put "</style>" NL;
finish:
put "<xml>" NL;
put "<o:DocumentProperties>" NL; ;
put " <o:Subject>OVD_Reports</o:Subject>" nl;
put " <o:Author>Robert Sayre</o:Author>" nl;
put " <o:Company>Acxiom</o:Company>" nl;
put " </o:DocumentProperties>" nl;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>OVD Enrollee Report</x:Name>" NL;
/*
put " <x:WorksheetOptions>" NL;
put " <x:FitToPage/>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:Print>" NL;
put " <x:ValidPrinterInfo/>" NL;
put " <x:PaperSizeIndex>5</x:PaperSizeIndex>" NL;
put " <x:HorizontalResolution>600</x:HorizontalResolution>" NL;
put " <x:VerticalResolution>600</x:VerticalResolution>" NL;
put " </x:Print>" NL;
*/;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml>" NL;
put "</head>" NL;
end;
define event data;
put "<td";
put ' width=' cellwidth;
trigger preimage;
putq " class=" HTMLCLASS;
trigger align;
trigger style_inline;
trigger rowcol;
put ">";
put VALUE NL;
finish:
trigger postimage;
put "</td>" NL;
end;
end;
run;
ods markup file="OVD_rd&rundate..xls"
tagset=tagsets.test
headtext="<style> @page {mso-page-orientation:portrait;}
col {mso-width-source:auto;}</style>";
/****************************************************/;
/****************************************************/;
/******** write out todays report using proc tabulate ********/;
/****************************************************/;
/****************************************************/;
proc tabulate data=ovd_new order=formatted noseps contents=' '
style=[background=white foreground=black];
var counted;
class p_date preference_date tenure credit_card_on_file_flag;
format tenure tenure.
credit_card_on_file_flag $ccof.;
table p_date='' all='<b>Month Total</b>', counted=''*sum='Total
Enrollees'*f=comma10.
tenure=''*Credit_card_on_file_flag=''*counted=''*sum=''*f=comma10.
/misstext='0' box='Date';
Title1 "OVD Enrollees by Day";
footnote1 "<td align=left colspan=4><b><font size=3>*OVD Enrollment
determined by subscriptions with VMGR_Opt_in_flag = 'Y'</font></b></td>";
footnote2 "<td align=left colspan=4><b><font size=3>*US Retail
subscriptions only</font></b></td>";
footnote3 "<td align=left colspan=4><b><font size=3>Source: Acxiom
Corp.</font></b></td>";
ods proclabel='OVD Enrollees by Day';
run;
ods markup close;
Thanks for any help you can provide!!!
--Bob
