The SAS Output Delivery System and reporting techniques

ods tagsets.excelxp and creating combo boxes

Reply
New Contributor
Posts: 4

ods tagsets.excelxp and creating combo boxes

I have 5 columns at the end of my excel file that I would like users to fill in.  Patient enrolled = Yes or No.  So I want the user to see a combo box for each record and then they can select Yes or No.  Is there a way to create a combo box using ods tagsets.excelxp? 

SAS Super FREQ
Posts: 8,715

Re: ods tagsets.excelxp and creating combo boxes

Hi:

  I'm going to guess that the answer to that question is NO. SAS is not creating a "true, binary" Excel file when you use TAGSETS.EXCELXP. SAS is creating a Spreadsheet XML/Office 2003 XML file that Excel knows how to treat "like" an Excel workbook. But many capabilities of Excel (for example, setting a password or pull down menu choices) are not controllable from the SAS end of things. I just checked in Excel and the choice for a making a form with a combo box is under Developer-->Insert as shown in the screenshot. I do not believe this is possible with TAGSETS.EXCELXP. You may want to check with Tech Support.

  You might consider writing a VB script to post process the file and make the combo boxes for you, then save the file as XLS or XLSX.

cynthia

Attachment
New Contributor
Posts: 4

Re: ods tagsets.excelxp and creating combo boxes

Thanks Cynthia!

Grand Advisor
Posts: 17,287

Re: ods tagsets.excelxp and creating combo boxes

Personally, I wouldn't use combo boxes, but data validation to restrict it to a drop down list.

This way you can apply it to a large range of cells at once and control the error messages.

Apply data validation to cells - Excel - Office.com

See the section on

Restrict data to predefined items in a list

Same answer as Cynthia though, I don't think you can do this via tagsets.

New Contributor
Posts: 4

Re: ods tagsets.excelxp and creating combo boxes

Thank you for your response Reeza.  Regarding using/not using combo boxes - that all depends on what the programmer and/or end-users are trying to accomplish with the excel file. 

Contributor
Posts: 71

Re: ods tagsets.excelxp and creating combo boxes

What you can try is adding combo boxes to a spreadsheet in Excel exactly how you want it. Then save the spreadsheet as XML.

Open the XML text file in an editor, and locate the combo box XML definitions. There might be a way to embed them from the SAS side into your ExcelXP output file in those columns. Sounds messy, though...

New Contributor
Posts: 4

Re: ods tagsets.excelxp and creating combo boxes

Thank you for your response boschy.

Ask a Question
Discussion stats
  • 6 replies
  • 728 views
  • 0 likes
  • 4 in conversation