BookmarkSubscribeRSS Feed
billinAZ
Calcite | Level 5
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 ;
value $vickie
low - high = '';
run;


**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
options(autofilter='all');
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.;
run;

ods tagsets.excelxp close;

ods listing;
run;
1 REPLY 1
Cynthia_sas
SAS Super FREQ
Hi:
The autofilter capability is something that TAGSETS.EXCELXP is turning on -- it is designed (by Microsoft) to filter the data that is -already- in a spreadsheet.
(see: http://www.wikihow.com/Use-AutoFilter-in-MS-Excel )

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.

Excel/Office have some Microsoft-based technologies that you might want to investigate:
1) forms or data entry forms
2) web query
3) Excel macro, VBA program, VB script or ActiveX program that solicits the state name and then opens the ExcelXP-created file and creates a new sheet or workbook based on extracting rows from the workbook with everything. Here are some links to get you started.
http://www.siop.org/tip/backissues/April%2004/pdf/414_061to069.pdf
http://www.ehow.com/how_4473738_create-forms-using-excel.html
http://office.microsoft.com/en-us/excel-help/creating-a-data-entry-form-with-the-excel-template-wiza...
http://support.microsoft.com/kb/157482
Wittwer, J.W., "Excel Web Query Secrets Revealed" From Vertex42.com, 2003, http://www.vertex42.com/News/excel-web-query.html
http://www.ozgrid.com/VBA/inputbox.htm
http://en.allexperts.com/q/Excel-1059/2008/3/prompt-user-enter-date.htm
http://excel.tips.net/Pages/T002266_Getting_User_Input_in_a_Dialog_Box.html
http://www.eggheadcafe.com/software/aspnet/33967973/copy-to-another-workbook.aspx


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.

cynthia

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
  • 1 reply
  • 1608 views
  • 0 likes
  • 2 in conversation