topic Re: PROC Print By Variables on separate tabs in Excel (SAS EG) in SAS Procedures

PROC Print By Variables on separate tabs in Excel (SAS EG)

TashaChapWUSS — Fri, 09 Oct 2020 17:59:58 GMT

Hey guys!

So, I was using PROC PRINT in SAS EG (output using the default Excel report output options), and I noticed that it was printing each BY Variable on a separate tab, which is *not* what I want it to do (waaaaaaaay too many tabs). I thought it required a PAGEBY statement for this kind of behavior?

Anyway, is there any way that I can have my report sectioned out by my BY Variable, but have the entire report appear on one tab?

FYI, I also tried this with an explicit ODS Excel statement to see if that made a difference, just in case there was a weird option hiding in the SAS EG default that I wasn't aware of and it came out the same.

Here's the PROC PRINT code. As you can see, it's pretty simple.

proc print data=dataerrorname noobs;
by tk_ois_establish;
run;

Re: PROC Print By Variables on separate tabs in Excel (SAS EG)

TashaChapWUSS — Fri, 09 Oct 2020 18:10:36 GMT

Just a couple minor things that I forgot to mention.

The data is sorted by tk_OIS_establish and one other variable to the right.

I had read that NOBYLINE causes this kind of behavior (separate page per BY group), and although I had not specified that option, I tried an OPTIONS BYLINE statement just to be sure, but that made no difference either.

Re: PROC Print By Variables on separate tabs in Excel (SAS EG)

Reeza — Fri, 09 Oct 2020 18:11:57 GMT

Look at the SHEET_INTERVAL option that allows you to specify when a new sheet is started. Assuming you're using ODS EXCEL.

Re: PROC Print By Variables on separate tabs in Excel (SAS EG)

TashaChapWUSS — Fri, 09 Oct 2020 18:18:21 GMT

Whew, that worked. I set the SHEET_INTERVAL="NONE" in the ODS Excel statement, and that did it. Must have had a different default than I realized. Thanks!

Re: PROC Print By Variables on separate tabs in Excel (SAS EG)

ballardw — Fri, 09 Oct 2020 20:31:40 GMT

@TashaChapWUSS wrote:

Whew, that worked. I set the SHEET_INTERVAL="NONE" in the ODS Excel statement, and that did it. Must have had a different default than I realized. Thanks!

The default for Sheet_interval is "table". Since each of your by values creates a different table, at least as far as ODS output is concerned, each went to a separate sheet.

You would see different behavior if you ran Proc Univariate or some other procedure with multiple tables.

Then the BYGROUP option keeps all the tables of a single by value on a sheet.

Another option value to consider would be PROC to keep each procedure output on a single tab.