topic Re: How can i filter rows with a value with ods excel? in ODS and Base Reporting

How can i filter rows with a value with ods excel?

MIsabel — Fri, 09 Oct 2020 09:20:14 GMT

Hi,

I want to extract some information for the table sashelp.shoes in a excel file with the bellow instruction but I also want to filter the column "Product" (second column) for the values "Sandal" and "Boot" and at the end create an excel with all the observations and the table filter by the column "Product"

I want to have all observations but the default view to be one with just Sandal/Boots and if i want in the Excel see the other values for that column

How can I update my program to have all the observations and also included the filter?

ods excel file="C:\Reportes\Testshoes.xlsx" options(sheet_name="Test");
ods excel options (autofilter = 'all');
proc report data=sashelp.shoes; columns _all_;
run;
ods excel close;

If I update proc report as the bellow line, it's only export the rows with these values, and I need to export all the lines in the excel and selected the filter with these values in the Excel.

proc report data=sashelp.shoes(where=(product="Sandal" or product='Boot'));

I have updated my program and now it creates the filter but it's not executed when i open the excel and I don't know how it can be executed in an automatically way by the ods option and also i would like to include in the filter more values as Boot.

ods excel file="C:\Reportes\Testshoes.xlsx" options(sheet_name="Test");

ods excel options (autofilter='2' autofilter_values='Sandal');
proc report data=sashelp.shoes; columns _all_; run;
ods excel close;

Thanks in advance for your help

Re: How can i filter rows with a value with ods excel?

PaigeMiller — Wed, 07 Oct 2020 15:56:34 GMT

proc report data=sashelp.shoes(where=(product="Sandal" or product='Boot'));

Re: How can i filter rows with a value with ods excel?

Reeza — Wed, 07 Oct 2020 16:06:53 GMT

Do you want your table to have all observations but the default view to be one with just Sandal/Boots but you can add back in the others?

or

Do you want a table exported with just Sandal/Boots?

Re: How can i filter rows with a value with ods excel?

RichardDeVen — Tue, 13 Oct 2020 13:17:38 GMT

ODS EXCEL has no mechanism nor options settings that let you preselect the filter values for an autofilter column.

In EXCEL you would write VBA code to programmatically set the current 'checked' values in the drop downlist.

Example:

' VBA code
Dim items(1 to 2) as String
items(1) = "Sandal"
items(2) = "Boot"

ActiveSheet.ListObjects("Table1").range.AutoFilter Field:=2, Criteria1:=items, Operator:=xlFilterValues