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

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

jdveerapaneni — Tue, 13 Aug 2024 21:42:00 GMT

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 .

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

rudfaden — Thu, 15 Aug 2024 12:42:01 GMT

use the autofilter option in ods excel

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

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

jdveerapaneni — Thu, 15 Aug 2024 14:54:21 GMT

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).

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

rudfaden — Fri, 16 Aug 2024 06:41:07 GMT

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

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

Tom — Fri, 16 Aug 2024 13:22:48 GMT

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.

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

jdveerapaneni — Fri, 16 Aug 2024 14:29:16 GMT

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

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

jdveerapaneni — Fri, 16 Aug 2024 14:30:20 GMT

sorry forgot to add excel in previous message