i would like to create a spreadsheet that has a column that acts as a drop down selection value (for example: States). Users would be able to click on the drop down menu, select the state they want and that would populate the cell in the spreadsheet.
Currently I use autofilter which does give the dropdown capability, but also requires that each row of my spreadsheet already have a value (one of the states)...
has anyone done this? is it possible to have autofilter variable contain a list of states but not actually fill the cell until the user selects one of the values?
here is my code (using a format to try to blank out values for state in each row--doesn't work)
proc format ;
low - high = '';
**This is from SAS NOTE 38195: Apply filters to columns in the output created with the ExcelXP destination";
ods listing close;
*ods tagsets.excelxp file='filters.xls' style=statistical
ods tagsets.excelxp file='filters.xls' style=statistical
options(autofilter='4' sheet_name='Visit 1' absolute_column_width='15' Frozen_rowheaders='1' FROZEN_HEADERS='1' );
proc print data=&xlsname noobs;
var Name type length state;
format state $vickie.;
To me, it almost sounds like you are describing how the stored process interface works in the SAS Add-in for Microsoft Office. A stored process will take country (or state) as a parameter. The stored process is defined to hold that parameter. When the user (in Excel) runs the stored process, the first thing they see is a prompt window that asks them to pick the Country (or State). Then they click a button (OK) to run the stored process and the results for the state they wanted are returned to Excel in spreadsheet form.
This behavior is very like an HTML form - -where the user calls up a web page that is a form -- the form collects information -- maybe with checkboxes, radio boxes or drop down selection boxes. The user makes choices and clicks a button and then the program -behind- the HTML form will go get or do whatever the program was supposed do and the results are returned to the user's browser in the form of an HTML page.
I do not believe you can use TAGSETS.EXCELXP to build a form in the way you envision. Autofilter is meant to be turned on IF you want to filter the data or procedure results that ODS is putting into the spreadsheet. So your trick of blanking out the state name and then thinking that autofilter will "reveal" the states that are filtered just isn't how autofilter works.
If you have the SAS Enterprise Intelligence Platform (also known as the BI Platform) and the SAS Add-in to Microsoft Office, then I would recommend a different approach. But since you said you were using TAGSETS.EXCELXP as your destination, I figure you are using Base SAS.