topic Re: option autofilter in SAS Programming

option autofilter

snip — Mon, 25 Sep 2023 10:51:00 GMT

Good morning,
I'm trying to use a proc tabulate on my table.
the autofilter option is not working properly on my group by and I don't know why.
I group by col1 col2 col3 col4 in my table, I have the autofilter which is set up in my Excel output, but if I filter for example my column COL1 on its first value to analyze my results, it shows me that only one line everywhere, yet this first line in col1 corresponds to several lines in col2 col3... since it is a group by
Could you help me ?

options( autofilter = "1-4" frozen_headers = "on" frozen_rowheaders = "4" /*start_at='A2'*/ sheet_interval='none' sheet_name='C5');

PROC TABULATE data=STATS;
class COL1 COL2 COL3 COL4  /ORDER=DATA;
classlev  COL1 COL2 COL3 COL4  ;
VAR  COL5 COL6;
TABLE   COL1*COL2*COL3*COL4,COL5 COL6 
;RUN;

ods excel close;

Re: option autofilter

SASJedi — Mon, 25 Sep 2023 12:13:07 GMT

When I run this code:

ods excel file="test.xlsx"
			options( autofilter = "1-2" 
						frozen_headers = "on" 
						frozen_rowheaders = "2" 
						sheet_interval='none' 
						sheet_name='C5');

PROC TABULATE data=sashelp.cars;
class ORIGIN MAKE /ORDER=DATA;
classlev  ORIGIN MAKE;
VAR  MPG_CITY MPG_HIGHWAY;
TABLE   ORIGIN*MAKE,MPG_CITY MPG_HIGHWAY;
RUN;

ods excel close;

The resulting Excel file has autofilters set for the specified columns:

Where I select only Asia for the first column filter in Excel, only one row shows

This is because, in the report, only one row contains the value "Asia" in the first column. The rows underneath do not contain any values, so they are filtered out by Excel. This is the way Excel filters work. What were you expecting / how do you want it to work?

Re: option autofilter

snip — Mon, 25 Sep 2023 12:53:56 GMT

Thank you for your reply.
I want if I filter a group, the whole group must be displayed.
if I filter Asia I must have in front:
Acura
Honda
Hyundai
Infiniti
Isuzu
Kia
Lexus
Mazda
Mitsubishi
Nissan
Scion
Subaru
Suzuki
Toyota

Re: option autofilter

SASJedi — Tue, 26 Sep 2023 20:51:10 GMT

In an Excel spreadsheet, if you want to select "Asia" as the auto-filter value for the first column and see ALL of the rows associated with Asia, then every row associated with Asia must have the word 'Asia' in the cell, like this:

This behavior is a function of how Excel filters data and is nothing you can control from a SAS program. As far as I know, PROC TABULATE doesn't make output like that, but others more familiar with the PROC might have input.

I'd approach this using PROC SQL instead because it can produce the result shown above:

ods excel file="test.xlsx"
			options( autofilter = "1-2" 
						frozen_headers = "on" 
						frozen_rowheaders = "2" 
						sheet_interval='none' 
						sheet_name='C5-sql');
proc sql;
select Origin
		,Make
		,SUM(MPG_CITY) 'MPG (City) Sum'
		,SUM(MPG_HIGHWAY) 'MPG (Highway) Sum'
	from sashelp.cars
	group by Origin, Make
	order by Origin, Make
;
quit;

ods excel close;

All the best,
Mark

Re: option autofilter

snip — Wed, 27 Sep 2023 14:54:38 GMT

THANKS,
I had already output this result, but it's not what I'm looking for.