Drop down Y/N (data validation list) in Excel using PROC REPORT and ODS in SAS

[jdveerapaneni]

I am using PROC Report  and ODS in SAS EG to generate Excel (XLSX) file.I have a column called Conversion_indicator which is blank column , i need to have Y/N values in the drop down list for all the cells where SSN is populated.so that when we send the report to end user, they should be only allowed to populate Y/N in those cells .

Is it possible with proc report ? I am currently using data validation list in excel and doing it manually.

It will be very helpful if i can automate it while generating the Excel report .

[rudfaden]

use the autofilter option in ods excel

 ods excel options(autofilter='all' sheet_name='filter all');
proc print data=sashelp.shoes(obs=20);
run;

[jdveerapaneni]

if we use auto filter option it just add filter for all columns , but i need Data validation filter (Y/N) for just 1 column which has blank data.so that end user can update excel with Y/N only (no other values in that column).

[rudfaden]

Can you give an example of how that is done in Excel?

[Tom]

I tried searching for this functionality of Excel on-line and all I could find were instructions on how to create the data validation rules using point and click methods in the Excel interface.  So it is not at all clear to me how Excel is doing this.  Where they are storing these data validation rules in the file.   But perhaps if you could figure out how it is done you could modify the XML that is stored in the XLSX file (an XLSX file is a ZIP file that contains special XML files) to create the data validation rules after the worksheet has been made.

[jdveerapaneni]

attached is the Excel (check column Convert_ind - only Yes/No values are allowed) with has data validation include in it. Excel data valaidation > settings > allow > list & range

or  below is the VBA macro i used to run manually after creating the excel file. If you can let me know if there is a way to call this VBA in SAS EG directly using ODS that will be helpful too.

Note - I have multiple sheets, doing it manually is not feasible. That the reason i need to do this while creating the excel in SAS .

Sub Macro1()
With Range("D:D").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

[jdveerapaneni]

sorry forgot to add excel in previous message