SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
jdveerapaneni
Calcite | Level 5

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 .

 

jdveerapaneni_0-1723585288567.png

 

6 REPLIES 6
rudfaden
Lapis Lazuli | Level 10

 

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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
Calcite | Level 5

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1112 views
  • 0 likes
  • 3 in conversation