outputting to Excel with page breaks when the value of a variable changes

[stateworker]

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.

Here's a mockup of what they have:

valuea valueb valuec
123 1 abc
321 1 dafd
432 1 fda
adf 2 vdfdas
dafda 2 fdafsa
fdaf 3 afdafda
fdaf 3 fdafda


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.

Any ideas?

[stateworker]

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.

And in case anyone was interested: http://www.mrexcel.com/td0032.html is where the tip is for Excel. (hope it's ok to post the link).

[Cynthia_sas]

Hi:
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.

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.

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.

This paper on using TAGSETS.EXCELXP is very useful in explaining all the print options available to you:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

[stateworker]

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.

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.