Data Validation Lists in SAS ODS tagsets.excelxp / proc report / proc template

Question

Am hoping that someone might point me in the right direction.

I am creating a spreadsheet using some elements of proc template and outputting it with a proc report within an ods tagsets.excelxp. This spreadsheet will be sent out in order to collect some data. I would like to limited the data collection entries to Yes or No.

Is it possible to create a data validation list just like Excel as per the image below (a drop down menu with limited values)?

I have checked the ods tagsets.excelxp options but nothing points to this. Would I need to create this using Proc Template? If so, is there any code I could look at ?

Thanks

Rbrt

Answer 1

TAGSETS.EXCELXP does not support data validation directly, as far as I know; see for example this question over on SAS Communities.

Chevelle Parker's paper for SUGI 28, Generating Custom Excel Spreadsheets Using ODS, provides a way to do it - but not using tagsets.excelxp, rather by writing a new tagset. I don't know why this option wasn't included in tagsets.excelxp; perhaps it has some issues, or the method shown here isn't consistent with how tagsets.excelxp was written. It might be worth considering customizing tagsets.excelxp to include this, though if that's your first tagset it's a very difficult one to wrap your head around as it has a lot of parts.