https://communities.sas.com/t5/SAS-Programming/Reading-Excel-pivot-tables-into-SAS-after-setting-a-dropdown/m-p/878405#M347046 <P>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?</P> <P>&nbsp;</P> <P>This is the dropdown menu in cell B1, set to zero (which is not what I want)</P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PaigeMiller_0-1685535224254.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/84521i4CAD5C395EDDCA77/image-size/medium?v=v2&amp;px=400" role="button" title="PaigeMiller_0-1685535224254.png" alt="PaigeMiller_0-1685535224254.png" /></span></P> <P>&nbsp;</P> Wed, 31 May 2023 12:33:40 GMT PaigeMiller 2023-05-31T12:33:40Z https://communities.sas.com/t5/SAS-Programming/Reading-Excel-pivot-tables-into-SAS-after-setting-a-dropdown/m-p/878405#M347046 <P>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?</P> <P>&nbsp;</P> <P>This is the dropdown menu in cell B1, set to zero (which is not what I want)</P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PaigeMiller_0-1685535224254.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/84521i4CAD5C395EDDCA77/image-size/medium?v=v2&amp;px=400" role="button" title="PaigeMiller_0-1685535224254.png" alt="PaigeMiller_0-1685535224254.png" /></span></P> <P>&nbsp;</P> Wed, 31 May 2023 12:33:40 GMT https://communities.sas.com/t5/SAS-Programming/Reading-Excel-pivot-tables-into-SAS-after-setting-a-dropdown/m-p/878405#M347046 PaigeMiller 2023-05-31T12:33:40Z https://communities.sas.com/t5/SAS-Programming/Reading-Excel-pivot-tables-into-SAS-after-setting-a-dropdown/m-p/878490#M347080 <P>Paige, I don't think you'll be able to get SAS to set this for you.</P> <P>&nbsp;</P> <P>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.</P> Wed, 31 May 2023 16:55:35 GMT https://communities.sas.com/t5/SAS-Programming/Reading-Excel-pivot-tables-into-SAS-after-setting-a-dropdown/m-p/878490#M347080 ChrisHemedinger 2023-05-31T16:55:35Z