topic Re: Excel - print output across the page in ODS and Base Reporting

Excel - print output across the page

RB1Kenobi — Mon, 11 Sep 2017 09:26:24 GMT

Hello

I have SAS 9.4 on a Windows environment.

We have been using DDE to print multiple output onto a worksheet where the output is placed side by side across the worksheet. However we would like to move away from DDE as it seems to be slow on the latest Windows version to the point of being unusable.

I don't believe ods tagsets.ExcelXP permits side-by-side layout

Does ODS Excel within 9.4 permit side-by-side layout, and if so how is this specified please?

Thanks

Re: Excel - print output across the page

RW9 — Mon, 11 Sep 2017 09:54:16 GMT

An example might help. Why can you not create the output files, and join them together? Quite simple you could even use Excel nomencalture, eg:

dataset1 ROWNO A B C D

dataset2 ROWNO E F G H

Merged:

ROWNO A B C D E F G H

You could also try the option;

PAGE_ORDER_ACROSS

If set to yes, the worksheet page order will be set to print across, then down

Look in the guidance:
https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

Re: Excel - print output across the page

RB1Kenobi — Mon, 11 Sep 2017 10:15:30 GMT

Thanks RW9. I am trying to mimic the level of output location control that DDE provides.

I had considered joining the output together and using proc report styling hacks to make it appear as two outputs. But this might not always work as we have many reports that have been built using DDE over the years.

The tagset option PAGE_ORDER_ACROSS creates one output per worksheet, whereas I am trying to control multiple SAS outputs within one worksheet e.g. the audience wants to compare tables side by side when printed on paper.

Re: Excel - print output across the page

RW9 — Mon, 11 Sep 2017 10:27:00 GMT

Not with tagset. You can try this:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Never used it myself. If it was me, I would change the whole process, never seen the point in multiple outputs on one sheet, or big Excel files. Excel is really not a reporting environment, not a data transfer file format etc.

Re: Excel - print output across the page

Cynthia_sas — Mon, 11 Sep 2017 13:38:09 GMT

Hi:
To me, the key part of your statement is: "the audience wants to compare tables side by side when printed on paper" -- so why not give them a PDF output that will allow them to do exactly this. ODS PDF does allow you to print tables side by side -- the simple way -- using COLUMNS=2 or the more complicated way -- using ODS LAYOUT and ODS REGION.

ODS PDF was originally written with the underlying assumption that the output could be printed. That is not necessarily true of the other destinations.

cynthia

Re: Excel - print output across the page

RB1Kenobi — Mon, 11 Sep 2017 16:08:57 GMT

Yes I agree, but I guarantee when I distribute the report the first response will be to ask for it in Excel! We can only try : )

Re: Excel - print output across the page

ballardw — Mon, 11 Sep 2017 22:38:33 GMT

Maybe not ideal but this shows a combined data set split across by the data set name. Use of IN= options in the Work.All would allow conditional assignment of better text than the data set name. To compare data the two sets should be sorted the same.

data work.one;
   input grp x y;
datalines;
1 3 28
1 2 56
1 5 33
;
run;
data work.two;
   input grp x y;
datalines;
1 3 28
1 2 56
1 5 23
;
run;

data work.all;
   length source $42.;
   set work.one work.two indsname=temp;
   source = temp;
run;

proc report data=work.all;
   column source,(grp x y);
   define source /across;
run;
quit;

Re: Excel - print output across the page

RB1Kenobi — Tue, 12 Sep 2017 12:35:38 GMT

Thank you for your input, that's an interesting datastep trick.