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;
THANKS,
I had already output this result, but it's not what I'm looking for.
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?
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
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
THANKS,
I had already output this result, but it's not what I'm looking for.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: