I am trying to read monthly Excel pivot tables into SAS (there are about 100 such Excel files), and create a single SAS data set. Normally, I can do this via macros or CALL EXECUTE without needing help. But the part that has me stumped is that there is a dropdown menu in Excel which is set to 0 for all of these spreadsheets (see below), and I need to obtain the values when this dropdown menu is set to "All". Can I set the dropdown menu to "All" using SAS programmatically, or do I have to go into each pivot table and manually set the dropdown menu to what I want and then manually re-save the Excel files, so I can then read them properly?
This is the dropdown menu in cell B1, set to zero (which is not what I want)
Paige, I don't think you'll be able to get SAS to set this for you.
You could automate the process using VB Script or PowerShell -- plenty of examples around that. You can automate pretty much any action that you can do in the UI. You can record the action as an Excel macro and examine the script to get a view into what it takes.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.