BookmarkSubscribeRSS Feed
Lorelle
Calcite | Level 5

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? 

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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


excel_insert_combo_box.png
Lorelle
Calcite | Level 5

Thanks Cynthia!

Reeza
Super User

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.

Lorelle
Calcite | Level 5

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. 

boschy
Fluorite | Level 6

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

Lorelle
Calcite | Level 5

Thank you for your response boschy.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2385 views
  • 0 likes
  • 4 in conversation