https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46520#M6080 Hi. I've been asked to help someone with an Excel file that they want to have page-breaks put when the value in one of their columns changes. I was thinking that perhaps we could pop it into SAS and then SAS could work its magic and output something that would meet their needs.<BR /> <BR /> Here's a mockup of what they have:<BR /> <BR /> valuea valueb valuec<BR /> 123 1 abc<BR /> 321 1 dafd<BR /> 432 1 fda<BR /> adf 2 vdfdas<BR /> dafda 2 fdafsa<BR /> fdaf 3 afdafda<BR /> fdaf 3 fdafda<BR /> <BR /> <BR /> They want a page break when valueb changes. So a page break between valueb=1 and valueb=2 and between valueb=2 and valueb=3. The values for this column are character, not numeric, but I don't think that would matter.<BR /> <BR /> Any ideas? Mon, 25 Jan 2010 18:11:50 GMT stateworker 2010-01-25T18:11:50Z https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46520#M6080 Hi. I've been asked to help someone with an Excel file that they want to have page-breaks put when the value in one of their columns changes. I was thinking that perhaps we could pop it into SAS and then SAS could work its magic and output something that would meet their needs.<BR /> <BR /> Here's a mockup of what they have:<BR /> <BR /> valuea valueb valuec<BR /> 123 1 abc<BR /> 321 1 dafd<BR /> 432 1 fda<BR /> adf 2 vdfdas<BR /> dafda 2 fdafsa<BR /> fdaf 3 afdafda<BR /> fdaf 3 fdafda<BR /> <BR /> <BR /> They want a page break when valueb changes. So a page break between valueb=1 and valueb=2 and between valueb=2 and valueb=3. The values for this column are character, not numeric, but I don't think that would matter.<BR /> <BR /> Any ideas? Mon, 25 Jan 2010 18:11:50 GMT https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46520#M6080 stateworker 2010-01-25T18:11:50Z https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46521#M6081 A former coworker who is an Excel whiz sent me a link on how to do it in Excel - but I'm still wondering about how we might be able to output with SAS.<BR /> <BR /> And in case anyone was interested: <A href="http://www.mrexcel.com/td0032.html" target="_blank">http://www.mrexcel.com/td0032.html</A> is where the tip is for Excel. (hope it's ok to post the link). Mon, 25 Jan 2010 19:00:36 GMT https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46521#M6081 stateworker 2010-01-25T19:00:36Z https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46522#M6082 Hi:<BR /> I'm not sure what you mean by "page break" since there are several different ways to interpret that. However, if you wanted to start a new worksheet every time VALUEB changed, then you should investigate the SHEET_INTERVAL suboption with TAGSETS.EXCELXP.<BR /> <BR /> If you want -printing- control over the data in one worksheet, then I believe that you may have to use Excel techniques to insert page breaks into a worksheet (such as applying an Excel macro to a sheet). Since in the Excel world, you have to be in a special PAGE VIEW in order to get the menu to insert page breaks.<BR /> <BR /> However, for the definitive response on how to insert page breaks (or whether it's possible) using TAGSETS.EXCELXP, you should consider opening a track with Tech Support.<BR /> <BR /> This paper on using TAGSETS.EXCELXP is very useful in explaining all the print options available to you:<BR /> <A href="http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf" target="_blank">http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf</A><BR /> <BR /> cynthia Mon, 25 Jan 2010 19:41:29 GMT https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46522#M6082 Cynthia_sas 2010-01-25T19:41:29Z https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46523#M6083 Cynthia - thanks for the info. They did want a page break as in just where it would print on a separate piece of paper. They have 1,000 clients and need to print out (I know, I know, I'm screaming inside about that!) for each client rather than having a separate worksheet that they could just send electronically. They had everything in one file.<BR /> <BR /> I'll def. take a look at the info you suggest as I'm certain I'll be able to use it for other things. Thanks again. Tue, 26 Jan 2010 14:13:34 GMT https://communities.sas.com/t5/ODS-and-Base-Reporting/outputting-to-Excel-with-page-breaks-when-the-value-of-a/m-p/46523#M6083 stateworker 2010-01-26T14:13:34Z