topic Re: ods excel options(flow="tables") and custom sorting in ODS and Base Reporting

ods excel options(flow="tables") and custom sorting

js5 — Tue, 22 Oct 2019 14:40:03 GMT

Hello,

I have just found the nead flow="tables" option to ods excel which prevents the insertion of newlines making tables much easier to reformat. Unfortunately, there is also a side effect: one can no longer custom sort the values by inserting spaces into the format such as this:

proc format;
    value $custsort
        "2" = "  2"
        "1" = " 1"
        "3" = "3";

This is because enabling the flow option disables the automatic stripping of the values resulting in the sorting spaces being visible in the final output. Is there a way of preventing the newline insertion and keeping the values stripped at the same time? Thank you!

Re: ods excel options(flow="tables") and custom sorting

ballardw — Tue, 22 Oct 2019 14:46:50 GMT

How are you creating the tables that you are displaying?

A few procedures, such as Proc Tabulate and Report, allow use of PRELOADFMT that will maintain an order if the format is defined with the Notsorted option and the correct options are chosen.

Re: ods excel options(flow="tables") and custom sorting

js5 — Tue, 22 Oct 2019 15:12:47 GMT

The tables are created with proc report. I am then using the format similar to the one displayed above for the group variables:

ETA: added code example

proc format;
	value $custsort
		"USA" = "  USA"
		"Asia" = " Asia"
		"Europe" = "Europe";
quit;

ods excel file="C:\Users\&sysuserid\test.xlsx" options(flow="tables");

proc report data=sashelp.cars;
	columns Origin Type, Horsepower;
	define Origin / " " group format=$custsort.;
	define Type / " " across;
	define Horsepower / " " analysis max;
run;

ods excel close;

Re: ods excel options(flow="tables") and custom sorting

ballardw — Tue, 22 Oct 2019 16:10:03 GMT

With proc report you are golden. Consider:

proc format;
	value $custsort (notsorted)
		"USA" = "  USA"
		"Asia" = " Asia"
		"Europe" = "Europe";
quit;



proc report data=sashelp.cars;
	columns Origin Type, Horsepower;
	define Origin / " " group preloadfmt order=data format=$custsort.;
	define Type / " " across;
	define Horsepower / " " analysis max;
run;

You can use the same approach for any format and shouldn't need the inserted spaces you had used. Do note that the format definition needs the Notsorted option and there are restrictions to use Preloadfmt thought the order=data is likeliest. This will also provide a row for the output for the format even if no values are encountered.

Re: ods excel options(flow="tables") and custom sorting

Ksharp — Wed, 23 Oct 2019 12:28:24 GMT

Or try other blank character like TAB character ?

proc format;
	value $custsort
		"USA" = "'09'x '09x' USA"
		"Asia" = "'09'x  Asia"
		"Europe" = "Europe";
quit;

Re: ods excel options(flow="tables") and custom sorting

ballardw — Wed, 23 Oct 2019 22:21:35 GMT

@Ksharp wrote:

Or try other blank character like TAB character ?

proc format;
	value $custsort
		"USA" = "'09'x '09x' USA"
		"Asia" = "'09'x  Asia"
		"Europe" = "Europe";
quit;

And I though I was bad using ASCII null characters, hold down ALT key and enter 255 on the numeric pad in Windows, to force indents before style ASIS was available. One headache I ran into with this approach was remembering that I had typed nulls and not spaces so extra comments were a very good idea.

The OP wanted the order but not the appearance of leading spaces and I can see the results of tabs as spaces in Excel.

Re: ods excel options(flow="tables") and custom sorting

js5 — Thu, 24 Oct 2019 07:04:22 GMT

Thanks @ballardw, your suggestion works! I am not getting rows for empty categories though with this code:

proc format;
	value $custsort (notsorted)
		"Dummy" = "Dummy"
		"USA" = "USA"
		"Asia" = "Asia"
		"Europe" = "Europe";
quit;

ods excel file="C:\Users\&sysuserid\test.xlsx" options(flow="tables");

proc report data=sashelp.cars;
	columns Origin Type, Horsepower;
	define Origin / " " group preloadfmt order=data format=$custsort.;
	define Type / " " across;
	define Horsepower / " " analysis max;
run;

ods excel close;