Providing software solutions since 1976
FOCUS AREAS
Return to previous page
|
Base SAS
SAS Code: Sample Gallery of Programs for ODS and Excel
This page is linked from Sample Gallery of Programs for ODS and Excel, which in turn is linked from the PDF document Using ODS to Generate Excel Files.
/***********************************************************/
/* Window options are specified by specifying the various */
/* tags within the WorkSheetOptions element. The Nodes that*/
/* The Node names describe the options. Specifying titles */
/* generate a dialog box. */
/***********************************************************/
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 "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put "<!--[if gte mso 9]><xml>" NL;
put " <x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:Selected/>" NL;
put " <x:DoNotDisplayGridlines/>" NL;
put " <x:DoNotDisplayZeros/>" NL;
put " <x:DoNotDisplayHeadings/>" NL;
put " <x:DoNotDisplayOutline/>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put " <x:HideHorizontalScrollBar/>" NL;
put " <x:HideVerticalScrollBar/>" NL;
put " <x:HideWorkbookTabs/>" NL;
put "<x:DisplayFormulas/>" NL;
put " <x:WindowHeight>10005</x:WindowHeight>" NL;
put " <x:WindowWidth>10005</x:WindowWidth>" NL;
put " <x:WindowTopX>120</x:WindowTopX>" NL;
put " <x:WindowTopY>135</x:WindowTopY>" NL;
put " </x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
options center;
title;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/******************************************************/
/* Page setup worksheet options are specified in this */
/* example. Everything from displaying page breaks to */
/* scaling output. */
/******************************************************/
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 "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put "<x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:Print>" NL;
put " <x:BlackAndWhite/>" NL;
put " <x:DraftQuality/>" NL;
put " <x:ValidPrinterInfo/>" NL;
put " <x:PaperSizeIndex>5</x:PaperSizeIndex>" NL;
put " <x:Scale>85</x:Scale>" NL;
put " <x:HorizontalResolution>300</x:HorizontalResolution>" NL;
put " <x:VerticalResolution>300</x:VerticalResolution>" NL;
put " <x:Gridlines/>" NL;
put " <x:RowColHeadings/>" NL;
put " </x:Print>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
title "this is a test";
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css'
headtext="<style> @page {mso-page-orientation:landscape} </style>" ;
proc print data=sashelp.class;
run;
ods markup close;
/**************************************************************/
/* Print Titles are specified within the ExcelName element. */
/* Within the Name tag, Print_Titles is specified, and within */
/* the formula tag, you specify range to repeat on each page */
/**************************************************************/
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 "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put "<x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:Print>" NL;
put " <x:ValidPrinterInfo/>" NL;
put " </x:Print>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "<x:ExcelName>" NL;
put "<x:Name>Print_Titles</x:Name>" NL;
put "<x:SheetIndex>1</x:SheetIndex>" NL;
put "<x:Formula>=Sheet1!$3:$3</x:Formula>" NL;
put "</x:ExcelName>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
title;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/********************************************************/
/* The Print Area is specified by specifying PRINT_Area */
/* within the Name tag of the ExcelName element. The */
/* Formula tag defines the area to print. */
/********************************************************/
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 "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:Print>" NL;
put " <x:ValidPrinterInfo/>" NL;
put " </x:Print>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "<x:ExcelName>" NL;
put "<x:Name>Print_Area</x:Name>" NL;
put "<x:SheetIndex>1</x:SheetIndex>" NL;
put "<x:Formula>=Sheet1!$3:$3</x:Formula>" NL;
put "</x:ExcelName>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
title "this is a test";
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/************************************************************/
/* Headers and footers can be specified by specifying the */
/* mso-header-data and the mso-footer-data style attributes */
/************************************************************/
ods html file='temp.xls' stylesheet="temp.css"
headtext=
'<style>
@Page
{mso-header-data:”Page &P of &N”;
mso-footer-data:"&Lthis is the left&CPage &P&RThis is the
right&A&D&T&N&P"};
</style>';
proc print data=sashelp.class;
run;
ods html close;
/***************************************************************/
/* The below example generates Landscape output by adding the */
/* ValidPrinterInfo tag within the Print element. */
/***************************************************************/
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 "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put "<x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:Print>" NL;
put " <x:ValidPrinterInfo/>" NL;
put " </x:Print>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css'
headtext="<style> @page {mso-page-orientation:landscape;} </style>" ;
proc print data=sashelp.class;
title;
run;
proc print data=sashelp.class;
run;
ods markup close;
/**********************************************************************/
/* Legal page size can be generated by specifying the PageSizeIndex */
/* tag within the Print element. Specifying a value of 5 for this tag */
/* indicates legal page size. */
/**********************************************************************/
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 "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put "<x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:Print>" NL;
put " <x:ValidPrinterInfo/>" NL;
put " <x:PaperSizeIndex>5
</x:PaperSizeIndex>" NL;
put " </x:Print>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css'
headtext="<style> @page {mso-page-orientation:landscape} </style>" ;
proc print data=sashelp.class;
title;
run;
proc print data=one;
run;
ods markup close;
/**********************************************************/
/* Leading 0's can be maintained by modifying the default */
/* CSV tagset adding the "=" in front of the value. */
/**********************************************************/
proc template;
define tagset Tagsets.test;
parent=tagsets.csv;
notes "This is the CSV definition";
define event data;
put "," / if !cmp( COLSTART , "1" );
put '=' """" / if cmp( TYPE , "string" );
put VALUE;
put """" / if cmp( TYPE , "string" );
end;
end;
run;
data one;
x='0001';
run;
ods markup file="temp.csv" tagset=tagsets.test;
proc print data=one;
run;
ods markup close;
/* For a single column, you can use the below example */
data one;
x='="00001"';
run;
ods csv file='tempx.csv';
proc print data=one;
run;
ods csv close;
/*****************************************************/ /* Using ODS HTML, this merges titles across columns */ /*****************************************************/ ods html file="temp.xls"; title "<td align=center colspan=4> <font size=4><b>this is a test</b></font></td>"; proc print data=sashelp.class; run; ods html close;
/**********************************************************/
/* Removes spaces between titles by adding a margin of 0. */
/**********************************************************/
ods markup file="temp.xls"
headtext="<style> h1 {margin:0}</style>"
stylesheet="temp.css" tagset=tagsets.test;
proc print data=sashelp.class;
title "this is a test";
title2 "this is another test";
run;
ods markup close;
/*****************************************/
/* Modify Title Size and title alignment */
/* using CSS style properties within the */
/* the HEADTEXT= option. */
/*****************************************/
proc template;
define tagset tagsets.test;
parent=tagsets.htmlcss;
define event head1;
put "<h1";
put ">";
put VALUE;
put "</h1>" NL;
end;
end;
run;
ods markup file="temp.xls"
headtext="<style> h1 {font-size:8pt;text-align:left}</style>"
stylesheet="temp.css" tagset=tagsets.test;
proc print data=sashelp.class;
title "this is a test";
run;
ods markup close;
/*******************************************/
/* Modify Title Size for each title */
/* using CSS style properties on each title*/
/* */
/*******************************************/
ods markup file="temp.xls"
headtext="<style> h1 {margin:0}</style>"
stylesheet="temp.css" tagset=tagsets.test;
proc print data=sashelp.class;
title "<div style='font-size:8pt;font-weight:bold;font-style:italic'>
test</div>";
title2 "<div style='font-size:8pt;font-weight:bold;font-style:italic'>
test2</div>";
run;
ods markup close;
/********************************************/
/* This example makes use of Excel functions*/
/* such as the row() and column() functions */
/* to create a checkerboard effect. */
/********************************************/
Proc template;
define tagset tagsets.format;
Parent=tagsets.htmlcss;
define event doc;
start:
put '<html 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;
finish:
put "<xml>" NL;
put " <x:ConditionalFormatting>" NL;
put " <x:Range>$A1:$F20</x:Range>" NL;
put " <x:Condition>" NL;
put " <x:Value1>=mod(row(),3)=MOD(COLUMN(),3)</x:Value1>" NL;
put " <x:Format Style='background:#ffffff'/>" NL;
put " </x:Condition>" NL;
put " <x:Condition>" NL;
put " <x:Value1>=mod(row(),2)=MOD(COLUMN(),2)</x:Value1>" NL;
put " <x:Format Style='background:#33CCCC'/>" NL;
put " </x:Condition>" NL;
put "</x:ConditionalFormatting>" NL;
put "</xml>" NL;
put "</head>" NL;
end;
end;
run;
ods tagsets.format file="c:\temp1.xls";
proc print data=sashelp.class;
title;
run;
ods tagsets.format close;
/********************************************/
/* The Sort nodes are specified within the */
/* Sorting element specifying each variable */
/* to sort. The descending tag is specified */
/* to sort by descending variable. */
/********************************************/
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 "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put '<!--[if gte mso 9]><xml>' NL;
put ' <x:ExcelWorkbook>' NL;
put ' <x:ExcelWorksheets>' NL;
put ' <x:ExcelWorksheet>' NL;
put ' <x:Name>Sheet1</x:Name>' NL;
put ' <x:Sorting>' NL;
put ' <x:Sort>name</x:Sort>' NL;
put ' <x:Descending/>' NL;
put ' <x:Sort>sex</x:Sort>' NL;
put ' <x:Descending/>' NL;
put ' <x:Sort>age</x:Sort>' NL;
put ' </x:Sorting>' NL;
put ' </x:ExcelWorksheet>' NL;
put ' </x:ExcelWorkbook>' NL;
put ' </xml><![endif]-->' NL;
put '</head>' NL;
end;
end;
run;
title;
ods markup path="c:\"(url=none) file="temp.xls" tagset=tagsets.test
stylesheet='temp.css';
proc print data=sashelp.class;
run;
ods Markup close;
/****************************************/
/* The below example Protect Worksheets */
/****************************************/
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put VALUE NL;
put "<style>" NL "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put " <!--[if gte mso 9]><xml>" NL;
put " <x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:ProtectContents>True</x:ProtectContents>" NL;
put " <x:ProtectObjects>True</x:ProtectObjects>" NL;
put " <x:ProtectScenarios>True</x:ProtectScenarios>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet> " NL;
put " </x:ExcelWorkbook>" NL;
put " </xml><![endif]--> " NL;
put "</head>" NL;
end;
end;
run;
options center;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/****************************************/ /* The below example protects Workbooks */ /****************************************/
/*******************************************************************/
/* The below example controls the height and width of the Workbook */
/*******************************************************************/
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put VALUE NL;
put "<style>" NL "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put " <!--[if gte mso 9]><xml>" NL;
put " <x:ExcelWorkbook>" NL;
put " <x:WindowHeight>8070</x:WindowHeight> " NL;
put " <x:WindowWidth>10380</x:WindowWidth> " NL;
put " <x:WindowTopX>480</x:WindowTopX> " NL;
put " <x:WindowTopY>120</x:WindowTopY> " NL;
put " </x:ExcelWorkbook>" NL;
put " </xml><![endif]--> " NL;
put "</head>" NL;
end;
end;
run;
options center;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/***************************************************/
/* The below example creates a backup of Copies */
/***************************************************/
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put VALUE NL;
put "<style>" NL "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put " <!--[if gte mso 9]><xml>" NL;
put " <x:ExcelWorkbook>" NL;
put " <x:CreateBackup/>" NL;
put " <x:WindowHeight>8070</x:WindowHeight> " NL;
put " <x:WindowWidth>10380</x:WindowWidth> " NL;
put " <x:WindowTopX>480</x:WindowTopX> " NL;
put " <x:WindowTopY>120</x:WindowTopY> " NL;
put " </x:ExcelWorkbook>" NL;
put " </xml><![endif]--> " NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/***************************************/
/* The below example splits the Screen */
/***************************************/
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put VALUE NL;
put "<style>" NL "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put "<!--[if gte mso 9]><xml>" nL;
put " <x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>xxx</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:Selected/>" NL;
put " <x:DoNotDisplayGridlines/>" NL;
put " <x:SplitHorizontal>2805</x:SplitHorizontal>" NL;
put " <x:TopRowBottomPane>2</x:TopRowBottomPane>" NL;
put " <x:SplitVertical>4950</x:SplitVertical>" NL;
put " <x:LeftColumnRightPane>2</x:LeftColumnRightPane>" NL;
put " <x:Panes>" NL;
put " <x:Pane>" NL;
put " <x:Number>3</x:Number>" NL;
put " </x:Pane>" NL;
put " <x:Pane>" NL;
put " <x:Number>1</x:Number>" NL;
put " <x:ActiveCol>5</x:ActiveCol>" NL;
put " </x:Pane>" NL;
put " <x:Pane>" NL;
put " <x:Number>2</x:Number>" NL;
put " <x:ActiveRow>14</x:ActiveRow>" NL;
put " <x:ActiveCol>4</x:ActiveCol>" NL;
put " </x:Pane>" NL;
put " <x:Pane>" NL;
put " <x:Number>0</x:Number>" NL;
put " <x:ActiveRow>14</x:ActiveRow>" NL;
put " <x:ActiveCol>7</x:ActiveCol>" NL;
put " </x:Pane>" NL;
put " </x:Panes>" NL;
put " <x:ProtectContents>False</x:ProtectContents>" NL;
put " <x:ProtectObjects>False</x:ProtectObjects>" NL;
put "<x:ProtectScenarios>False</x:ProtectScenarios>" NL;
put "</x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put " <x:WindowHeight>6030</x:WindowHeight>" NL;
put "<x:WindowWidth>10380</x:WindowWidth>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL;
put " <x:WindowTopY>135</x:WindowTopY>" NL;
put "<x:ProtectStructure>False</x:ProtectStructure>" nL;
put "<x:ProtectWindows>False</x:ProtectWindows>" NL;
put "</x:ExcelWorkbook>" nL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
options center;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/**********************************************************************/
/* The below code has the appearance of a native Excel or CSV file by */
/* displaying the gridlines in the worksheet. */
/**********************************************************************/
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 "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:Selected/>" NL;
put " <x:Gridlines/>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put " </x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\temp.xls" tagset=tagsets.test style=styles.test;
proc print data=sashelp.class;
title;
run;
ods markup close;
/************************************************************/
/* Generating page breaks in output can be done by adding */
/* The PageBreak element with the RowBreak tag. Within the */
/* RowBreak tag you specify the rows to add the page breaks */
/************************************************************/
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 "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put "<x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:PageBreaks>" NL;
put " <x:RowBreaks>" NL;
put " <x:RowBreak>" NL;
put " <x:Row>22</x:Row>" NL;
put " </x:RowBreak>" NL;
put " <x:RowBreak>" NL;
put " <x:Row>45</x:Row>" NL;
put " </x:RowBreak>" NL;
put " <x:RowBreak>" NL;
put " <x:Row>69</x:Row>" NL;
put " </x:RowBreak>" NL;
put " </x:RowBreaks>" NL;
put " </x:PageBreaks>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:Print>" NL;
put " <x:ValidPrinterInfo/>" NL;
put " </x:Print>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
edit styles.default as styles.test;
edit html /
'PageBreakLine' =
%nrstr("<p style=""page-break-after: always;""> </p>");
end;
run;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css'
style=styles.test
headtext="<style> @page {mso-page-orientation:landscape;} </style>" ;
proc print data=sashelp.class;
title;
run;
proc print data=sashelp.class;
run;
proc print data=sashelp.class;
run;
proc print data=sashelp.class;
run;
ods markup close;
/*******************************************************/
/* The below example changes the zoom property to 400% */
/* by modifying the WorsheetOptions element. */
/*******************************************************/
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put VALUE NL;
put "<style>" NL "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put " <!--[if gte mso 9]><xml>" NL;
put " <x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Sheet1</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:Zoom>400</x:Zoom>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet> " NL ;
put " <x:ExcelWorksheets>" NL;
put " </x:ExcelWorkbook>" NL;
put " </xml><![endif]--> " NL;
put "</head>" NL;
end;
end;
run;
options center;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/***********************************************************************/
/* Macro are executed when the workboook is opened using the auto_open */
/* method and executed when closed using auto_close. The formula tag */
/* points to the workbook with the macro to execute. */
/***********************************************************************/
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
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 "<!--[if gte mso 9]><xml>" nL;
put "<x:ExcelName>" NL;
put " <x:Name>auto_open</x:Name>" NL;
put " <x:Macro>Command</x:/Macro>" NL;
put " <x:Formula>=personal.xls!base
</x:Formula>" NL;
put "</x:ExcelName>" NL;
put "<x:ExcelName>" NL;
put " <x:Name>auto_close</x:Name>" NL;
put " <x:Macro>Command</x:/Macro>" NL;
put " <x:Formula>=personal.xls!cowboys
</x:Formula>" NL;
put "</x:ExcelName>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\temp.xls" tagset=tagsets.test;
proc print data=sashelp.class;
title;
run;
ods markup close;
/******************************************************/
/* The below example shows syntax for Data Validation */
/******************************************************/
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 '<!--[if gte mso 9]><xml>' NL;
put '<x:ExcelWorkbook>' NL;
put ' <x:ExcelWorksheets>' NL;
put ' <x:ExcelWorksheet>' NL;
put ' <x:Name>testing1</x:Name>' NL;
put ' <x:WorksheetOptions>' NL;
put ' <x:Selected/>' NL;
put ' <x:DoNotDisplayGridlines/>' NL;
put ' <x:Panes>' NL;
put ' <x:Pane>' NL;
put ' <x:Number>3</x:Number>' NL;
put ' <x:ActiveRow>3
</x:ActiveRow>' NL;
put ' <x:ActiveCol>1
</x:ActiveCol>' NL;
put ' </x:Pane>' NL;
put ' </x:Panes>' NL;
put ' <x:DataValidation>' NL;
put ' <x:Range>B4</x:Range>' NL;
put ' <x:Type>Whole</x:Type>' NL;
put ' <x:Qualifier>Less
</x:Qualifier>' NL;
put ' <x:Value>0</x:Value>' NL;
put ' <x:InputTitle>Tip
</x:InputTitle>' NL;
put ' <x:InputMessage>Verify number
</x:InputMessage>' NL;
put ' <x:ErrorMessage>incorrect
number </x:ErrorMessage>' NL;
put ' <x:ErrorTitle>stop
</x:ErrorTitle>' NL;
put ' </x:DataValidation>' NL;
put ' </x:ExcelWorksheet>' NL;
put ' </x:ExcelWorksheets>' NL;
put '</x:ExcelWorkbook>' NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\temp.xls" tagset=tagsets.test
stylesheet="temp.css";
proc print data=sashelp.class;
title;
run;
ods markup close;
/******************************************************************/
/* The below example freezes the column headers so that the column */
/* headers and the first row are frozen when you scroll across */
/* across and down the page. Within the WorkSheetOptions node, the */
/* the FreezePaynes tag is specified along with the other tags re- */
/* sponsible for where the row and column freeze. */
/*******************************************************************/
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 "<!--" NL;
trigger alignstyle;
put "-->" NL "</style>" NL;
finish:
put "<!--[if gte mso 9]>" NL;
put "<xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>temp</x:Name>" NL;
put " <x:WorksheetOptions>" NL;
put " <x:Selected/>" NL;
put " <x:DoNotDisplayGridlines/>" NL;
put " <x:FreezePanes/>" NL;
put " <x:FrozenNoSplit/>" NL;
put " <x:SplitHorizontal>2</x:SplitHorizontal>" NL;
put " <x:TopRowBottomPane>3</x:TopRowBottomPane>" NL;
put " <x:SplitVertical>1</x:SplitVertical>" NL;
put " <x:LeftColumnRightPane>1</x:LeftColumnRightPane>" NL;
put " <x:Panes>" NL;
put " <x:Pane>" NL;
put " <x:Number>0</x:Number>" NL;
put " </x:Pane>" NL;
put " </x:Panes>" NL;
put " </x:WorksheetOptions>" NL;
put " </x:ExcelWorksheet>" NL;
put "</x:ExcelWorksheets>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
options center;
title;
ods markup file="c:\temp.xls" tagset=tagsets.test stylesheet='c:\temp.css';
proc print data=sashelp.class;
run;
ods markup close;
/****************************************************************/
/* Code generates multiple worksheets per workbook. In this */
/* example, the .HTML, or .XLS files file have to already exist */
/* prior to adding the file with the WorksheetSource= attribute */
/* to the workbook. */
/****************************************************************/
/* Sample code to produce file to place in workbook, */
proc sort data=sashelp.class out=test;
by sex;
ods html file="c:\temp.html" newfile=bygroup;
proc report data=test nowd;
by sex;
run;
ods html close;
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put ' <meta name="Excel Workbook Frameset">' NL;
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>final1</x:Name>" NL;
put " <x:WorksheetSource HRef='c:\temp.html'/>" NL;
put " </x:ExcelWorksheet>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>exxml</x:Name>" NL;
put " <x:WorksheetSource HRef='c:\temp1.html'/>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put " <x:Stylesheet HRef='./stylesheet.css'/>" NL;
put " <x:WindowHeight>8070</x:WindowHeight>" NL;
put " <x:WindowWidth>10380</x:WindowWidth>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL;
put "<x:WindowTopY>45</x:WindowTopY>" NL;
put "<x:ActiveSheet>3</x:ActiveSheet>" NL;
put "<x:ProtectStructure>False</x:ProtectStructure>" NL;
put "<x:ProtectWindows>False</x:ProtectWindows>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\xx.xls" tagset=tagsets.test ;
data _null_;
file print;
put;
run;
ods markup close;
/**********************************************************************/
/* The below example generates multiple worksheets per workbook */
/* without generating a warning when opening. So that the warning */
/* is not generated, there are a couple of modifications that have */
/* to be made to the template. By default, because all of the files */
/* are not in the location that Excel expects, a warning is generated.*/
/* The worksheets generated need to be in a subdirectory with the */
/* base name of the workbook suffixed with an '_files'. Also a file */
/* with the name filelist.xml will need to reside in this same loc- */
/* of the workbook name suffixed with "_files". This is specified with*/
/* the <link> tag in the Doc_head event. The XML file is created with */
/* the data step and can contain anything. */
/**********************************************************************/
/* Create sample files */
/* Sort data set by sex */
proc sort data=sashelp.class out=newdsn;
by sex;
run;
/* Create a new HTML file for each distinct value of sex */
ods html file="c:\bygroup_files\temp.html" newfile=bygroup;
proc report data=newdsn nowd;
title;
by sex;
define sex / style={font_weight=bold};
run;
ods html close;
/* Create multiple worksheets per workbook */
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
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 ' <meta name="Excel Workbook Frameset">';
put '<link rel=File-List href="c:\bygroup_files/filelist.xml">' NL;
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Female</x:Name>" NL;
put " <x:WorksheetSource HRef='c:\bygroup_files/temp.html'/>" NL;
put " </x:ExcelWorksheet>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Male</x:Name>" NL;
put " <x:WorksheetSource HRef='c:\bygroup_files/temp1.html'/>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put " <x:Stylesheet HRef='c:\bygroup_files/temp.css'/>" NL;
put " <x:WindowHeight>8070</x:WindowHeight>" NL;
put " <x:WindowWidth>10380</x:WindowWidth>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL;
put "<x:WindowTopY>45</x:WindowTopY>" NL;
put "<x:ActiveSheet>2</x:ActiveSheet>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
data _null_;
file "c:\bygroup_files\filelist.xml";
put "test";
run;
ods markup file="c:\bygroup.xls" tagset=tagsets.test;
data _null_;
file print;
put "test";
run;
ods markup close;
dm 'wbrowse "c:\bygroup.xls"';
/***************************************************************************/
/* The below example dynamically creates multiple workbooks per worksheet */
/* for each by group. In the parameter for the macro add the by variable */
/* the SAS data set and the location to write the HTML files created. */
/* The filename and the Worksheet name will be the name of the by group */
/* */
/* Byvar= name of by variable */
/* dsn= name of data set */
/* dir= location to store worksheets */
/***************************************************************************/
options sgen mprint;
%macro test(byvar=sex,dsn=sashelp.class,dir=c:\temp\);
/* Create macro variable last with the distinct number of levels */
/* of variable X. */
proc sql noprint;
select count(distinct &byvar) into :last
from &dsn;
/* Create macro variables VAL1 to VAL4. The ending range gets the */
/* value from macro variable last. */
select distinct(&byvar) into: val1- :val%left(&last)
from &dsn;
run;
quit;
%put _user_;
/* Set up FILENAME which creates a new file for each by-group */
%do i = 1 %to &last;
ods htmlcss body="&dir.&&val&i...html";
proc print data=&dsn(where=(&byvar="&&val&i"));
run;
ods htmlcss close;
%end;
/* Loop through by-groups and add as worksheets */
%macro Worksheet;
%do i=1 %to &last;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>&&val&i</x:Name>" NL;
put " <x:WorksheetSource HRef='&dir.&&val&i...xls'/>" NL;
put " </x:ExcelWorksheet>" NL;
%end;
%mend;
/* Add worksheets to the Workbook */
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put ' <meta name="Excel Workbook Frameset">';
put '<meta http-equiv=Content-Type content="text/html;
charset=windows-1252">';
put '<meta name=ProgId content=Excel.Sheet>';
put '<meta name=Generator content="Microsoft Excel 9">';
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
%worksheet
put " </x:ExcelWorksheets>" NL;
put " <x:Stylesheet HRef='./stylesheet.css'/>" NL;
put " <x:WindowHeight>8070</x:WindowHeight>" NL;
put " <x:WindowWidth>10380</x:WindowWidth>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL;
put "<x:WindowTopY>45</x:WindowTopY>" NL;
put "<x:ActiveSheet>3</x:ActiveSheet>" NL;
put "<x:ProtectStructure>False</x:ProtectStructure>" NL;
put "<x:ProtectWindows>False</x:ProtectWindows>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
/* Create workbook which imports the worksheets */
ods markup file="c:\xx.xls" tagset=tagsets.test ;
data _null_;
file print;
put "test";
run;
ods markup close;
%mend;
%test( )
/*****************************************************************************/
/* The below macro will take all of the data sets in a library and create */
/* a separate worksheet for each one. The two parameters to this macro is */
/* Lib= which is the name of the library where the data sets current reside */
/* and DIR= which is the locations of where the files generated are stored. */
/* */
/* lib= library with data sets to store in workbook */
/* dir= locations to store files */
/*****************************************************************************/
%macro test(lib=sashelp,dir=c:\);
/* Create macro variable last with the distinct number of levels */
/* of variable X. */
proc sql;
select count(distinct memname) into :last
from dictionary.members
where libname=%UPCASE("&LIB") and memtype="DATA";
/* Create macro variables VAL1 to VAL4. The ending range gets the */
/* value from macro variable last. */
select distinct(memname) into: val1- :val%left(&last)
from dictionary.members
where libname=%upcase("&LIB") and memtype="DATA" ;
run;
quit;
%put _user_;
/* Creates a new file for each data set in the library */
%do i = 1 %to &last;
ods htmlcss path="&dir"(url=none) body="&&val&i...xls" stylesheet="&&val&i...css";
proc print data=&lib..&&val&i;
run;
ods htmlcss close;
%end;
/* Loop through by-groups and add as worksheets */
%macro Worksheet;
%do i=1 %to &last;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>&&val&i</x:Name>" NL;
put " <x:WorksheetSource HRef='&dir.&&val&i...xls'/>" NL;
put " </x:ExcelWorksheet>" NL;
%end;
%mend;
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put ' <meta name="Excel Workbook Frameset">';
put '<meta http-equiv=Content-Type content="text/html;
charset=windows-1252">';
put '<meta name=ProgId content=Excel.Sheet>';
put '<meta name=Generator content="Microsoft Excel 9">';
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
%worksheet
put " </x:ExcelWorksheets>" NL;
put " <x:Stylesheet HRef='./stylesheet.css'/>" NL;
put " <x:WindowHeight>8070</x:WindowHeight>" NL;
put " <x:WindowWidth>10380</x:WindowWidth>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL;
put "<x:WindowTopY>45</x:WindowTopY>" NL;
put "<x:ActiveSheet>3</x:ActiveSheet>" NL;
put "<x:ProtectStructure>False</x:ProtectStructure>" NL;
put "<x:ProtectWindows>False</x:ProtectWindows>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\xx.html" tagset=tagsets.test stylesheet='c:\temp.css';
data _null_;
file print;
put "test";
run;
ods markup close;
%mend;
%test( )
/***********************************************************************/
/* The below code takes a directory and an extension as arguments to */
/* to the macro. The directory is the directory that the .HTML or .XLS */
/* files are stored. The extension is the extension of the of the file */
/* in the library that we should be looking for. These files are */
/* are included into the final workbook as worksheets. */
/* */
/* directory- directory the files are located */
/* extensn - file to look for either XLS or HTML */
/***********************************************************************/
%macro getnames(directory=c:\temp\,extensn=xls);
filename xxx pipe "dir &directory*.&extensn.";
data two;
infile xxx length=l firstobs=6;
length filename $200;
input @;
input @1 filename $varying200. l;
filename=reverse(trim(left(filename)));
findspace=index(filename,' ');
filename=trim(left(reverse(substr(filename,1,findspace))));
if filename not in('bytes','free');
run;
filename xxx clear;
proc sql;
select count(filename) into: count
from two;
select filename into :val1 - :val%left(&count)
from two;
run;
quit;
%macro Worksheet;
%do i=1 %to &count;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>&&val&i</x:Name>" NL;
put " <x:WorksheetSource HRef='&directory.&&val&i'/>" NL;
put " </x:ExcelWorksheet>" NL;
%end;
%mend;
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put HTMLDOCTYPE NL NL NL;
put '<html xmlns:o="urn:schemas-microsoft-com:office:office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
put 'xmlns="http://www.w3.org/TR/REC-html40">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put ' <meta name="Excel Workbook Frameset">';
put '<meta http-equiv=Content-Type content="text/html;
charset=windows-1252">';
put '<meta name=ProgId content=Excel.Sheet>';
put '<meta name=Generator content="Microsoft Excel 9">';
finish:
put "<!--[if gte mso 9]><xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
%worksheet
put " </x:ExcelWorksheets>" NL;
put " <x:Stylesheet HRef='./stylesheet.css'/>" NL;
put " <x:WindowHeight>8070</x:WindowHeight>" NL;
put " <x:WindowWidth>10380</x:WindowWidth>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL;
put "<x:WindowTopY>45</x:WindowTopY>" NL;
put "<x:ActiveSheet>3</x:ActiveSheet>" NL;
put "<x:ProtectStructure>False</x:ProtectStructure>" NL;
put "<x:ProtectWindows>False</x:ProtectWindows>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\xx.xls" tagset=tagsets.test stylesheet='c:\temp.css';
data _null_;
file print;
put "test';
run;
ods markup close;
%mend;
%getnames( )
/**********************************************************************/
/* The below tagset uses the XML Spreadsheet format to generate */
/* the spreadsheet. This spreadsheet allows you to generate mul- */
/* tiple workbooks per worksheet by default. The output generated */
/* is generated in the XML spreadsheet format. To name the worksheets */
/* Use the ANCHOR= ODS MARKUP sub-option to specify the name of the */
/* Worksheet. This works in 8.2, but is pretty limited as to what it */
/* in some of the functionality. If you really like this format, */
/* consider upgrading to 9.1 where we have the ExcelXP tagset. Both */
/* Tagsets require Excel 2002, but ExcelXP tagset also requires 9.1 */
/**********************************************************************/
ods path(prepend) work.template(update);
proc template;
define tagset Tagsets.excel82;
notes "This is the default definition";
define event attr_out;
putq " label=" LABEL;
putq " clabel=" CLABEL;
putq " type=" TYPE;
putq " path=" PATH;
putq " title=" FLYOVER;
putq " class=" HTMLCLASS;
end;
define event doc;
start:
put "<?xml version=""1.0""";
putq " encoding=" encoding;
put "?>" NL NL;
put '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"' nl;
put 'xmlns:o="urn:schemas-microsoft-com:office:office"' nl;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' nl;
put 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"' nl;
put 'xmlns:html="http://www.w3.org/TR/REC-html40">' nl;
put '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">' nl;
put ' <WindowHeight>5775</WindowHeight>' NL;
put ' <WindowWidth>10380</WindowWidth>' NL;
put ' <WindowTopX>480</WindowTopX>' NL;
put ' <WindowTopY>60</WindowTopY>' NL;
put ' <ActiveSheet>1</ActiveSheet>' NL;
put ' <ProtectStructure>False</ProtectStructure>' NL;
put ' <ProtectWindows>False</ProtectWindows>' NL;
put '</ExcelWorkbook>' NL;
put ' <Styles>' NL;
put ' <Style ss:ID="Header" >' NL;
put ' <Alignment ss:Vertical="Bottom"/>' NL;
put ' <Borders>' nl;
put ' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"';
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Left" ss:LineStyle="None" ss:Weight="0"' ;
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Right" ss:LineStyle="None" ss:Weight="0"';
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"';
put ' ss:Color="#000000"/>' NL;
put ' </Borders>' NL;
PUT ' <Font ss:Size="12" ss:Color="#0000FF" />' nl;
PUT ' <Interior ss:Color="#D3D3D3" ss:Pattern="Solid"/>' NL;
PUT ' <NumberFormat/>' NL;
PUT ' <Protection/>' NL;
PUT ' </Style>' NL;
put ' <Style ss:ID="Data" >' NL;
put ' <Alignment ss:Vertical="Bottom"/>' NL;
put ' <Borders>' nl;
put ' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"';
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"' ;
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"';
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"';
put ' ss:Color="#000000"/>' NL;
put ' </Borders>' NL;
PUT ' <Font/>' nl;
PUT ' </Style>' NL;
put ' <Style ss:ID="RowHeader" >' NL;
put ' <Alignment ss:Vertical="Bottom"/>' NL;
put ' <Borders>' nl;
put ' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"';
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"' ;
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"';
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"';
put ' ss:Color="#000000"/>' NL;
put ' </Borders >' NL;
PUT ' <Font ss:Size="12" ss:Color="#0000FF" />' nl;
PUT ' <Interior ss:Color="#D3D3D3" ss:Pattern="Solid"/>' NL;
PUT ' <NumberFormat/>' NL;
PUT ' <Protection/>' NL;
PUT ' </Style>' NL;
put ' <Style ss:ID="DataEmphasis" >' NL;
put ' <Alignment ss:Vertical="Bottom"/>' NL;
put ' <Borders>' nl;
put ' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="3"';
put ' ss:Color="green"/>' NL;
put ' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"' ;
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"';
put ' ss:Color="#000000"/>' NL;
put ' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="3"';
put ' ss:Color="green"/>' NL;
put ' </Borders >' NL;
PUT ' <Font ss:Size="12" ss:Color="#0000FF" />' nl;
PUT ' <Interior ss:Color="#D3D3D3" ss:Pattern="Solid"/>' NL;
PUT ' <NumberFormat/>' NL;
PUT ' <Protection/>' NL;
PUT ' </Style>' NL;
PUT ' </Styles>' NL;
finish:
put "</Workbook>" NL;
end;
define event doc_title;
put "<title>" / if exist( VALUE );
put VALUE;
put "</title>" NL / if exist( VALUE );
end;
define event anchor;
put "<label";
putq " name=" anchor;
put "/>" NL;
end;
define event table;
start:
putq '<Worksheet ss:Name=' anchor ">" nl;
put "<ss:Table";
put ">" NL ;
finish:
put "</Table>" NL;
put '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">' NL;
put ' <Selected/>' NL;
put ' <DoNotDisplayGridlines/>' NL;
put ' <ProtectObjects>False</ProtectObjects>' NL;
put ' <ProtectScenarios>False</ProtectScenarios>' NL;
put '</WorksheetOptions>' NL;
put '</Worksheet>' NL;
end;
define event colspec_entry;
put "<ss:Column";
putq " ss:Width='50'" ;
put "/>" NL;
end;
define event row;
start:
put "<Row>" NL;
finish:
put "</Row>" NL;
end;
define event header;
start:
putq '<Cell ss:StyleID=' HTMLCLASS '><ss:Data ss:Type=';
put '"String"' / if cmp(type,"string");
putq '"Number"' / if !cmp(type,"string");
put ">";
put VALUE / if exist(value);
finish:
put "</Data></Cell>" NL;
end;
define event data;
start:
putq '<Cell ss:StyleID=' HTMLCLASS '><Data ss:Type=' ;
put '"String"' / if cmp(type,"string");
putq '"Number"' / if !cmp(type,"string");
put ">";
put value;
finish:
put "</Data></Cell>" NL;
end;
end;
run;
/** End of template**/
options nobyline;
ods markup file="c:\temp.xls" tagset=tagsets.excel82;
proc sort data=sashelp.class out=new;
by age;
ods Markup anchor="Print";
proc print data=sashelp.class;
run;
Ods Markup anchor="report";
proc REPORT data=sashelp.class NOWD;
RBREAK AFTER / SUMMARIZE;
compute after;
name="test";
endcomp;
run;
Ods Markup anchor="sql";
proc sql;
select * from sashelp.class;
run;
quit;
ods markup close ;
