BookmarkSubscribeRSS Feed
Diamond | Level 26

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 Miller
Community Manager

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. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation