Dropdown list for export to excel

[DanielP91]

I was wondering if it's possible to create a drop down list in SAS EG for export to excel. For example in my image below I would like to build a program where I can create a column which has a drop down list built into it when I export the data to an excel spreadsheet?  

Any advice will help. 

Regards

Daniel 

[Ksharp]

You need write code :

ods _all_ close;
ods excel file='c:\temp\temp.xlsx' options(autofilter='all');
proc report data=sashelp.class nowd;
run;
ods excel close;

[DanielP91]

Sorry I should have explained it a bit better. 

I want the ability to select 'Yes/No' as a list in empty cells not as a filter in the top row . Similar to when you create a list in excel. Any code that can make this happen? 

[SASKiwi]

Adding custom windows to EG is not possible, it is purely a SAS application development interface.

[AlanC]

Sorry, that is not true. EG is a C# app and interfaces with SAS using Integration Technoloies. You can create any UI elements using a custom task. Plenty of papers on it. You should use WPF. This requires C# development but there are lots of examples.

[SASKiwi]

@AlanC  - Thanks for the correction. I'd forgotten you can add custom tasks. However I suspect this would be like using a sledgehammer to crack a nut based on what the OP wants and given EG already has windowing interfaces for exporting to Excel.

[Ksharp]

Maybe @Cynthia_sas knows ?

[Cynthia_sas]

Unfortunately, I still don't understand what is needed here. My initial thought was the autofilter suboption. It almost sounds to me like this Excel sheet will be used for some kind of data entry if the Yes/No menu is appearing in an empty cell. I would guess that the ability to do that is an Excel-specific feature. Whether you could build that feature into SAS report output is an unknown to me. You can put a formula into a cell when you use the ODS EXCEL destination, but I don't know enough about the Yes/No capability in Excel without SAS in the picture to comment on whether it's possible or not. This may be a question that would get a definitive answer from Tech Support.
Cynthia

[Reeza]

Some older methods, none that I'd necessarily recommend.

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excel-data-validation/td-p/388441

Excel drop downs are called 'data validation' rules which can help with googling.

 @DanielP91 how are you creating the Excel file currently?

[DanielP91]

Hi All 

Thanks for the feedback. I am currently creating the excel file in SAS and exporting it with ODS proc report. Basically I want a unique column once the export is completed to have a data validation rule. This exported report will be used for data entry. 

Very similar to what the link Reeza provided below. 

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excel-data-validation/td-p/388441

If this is currently not possible with SAS 9.4 then I will just add this data validation rule once the report is exported to excel. 

[Reeza]

If you're doing this multiple times it's worth trying to get the data validation automated, but I'd recommend a VBS method instead of SAS/manual. Is EG running locally or on the server?

https://stackoverflow.com/questions/21788845/how-to-add-a-drop-down-list-in-excel-using-vbs

If you have X Command enabled (usually not an option) you can call it from SAS as well to apply it to the Excel file after you create it.

[DanielP91]

Thanks Reez. 

Yes this will mostly likely be a daily process. It think the solution mentioned below re running the method with VBS. 

Currently run SAS EG from a server. X command?

[Patrick]

I'm still unclear what you really want to achieve BUT if this is about creating an Excel that got a column with input validation as Excel drop downs then:

That's something I needed some time ago as well. As per my investigation it's not something that you can really do with SAS but you can do it with Python and it's no problem to call Python out of SAS, pass SAS data to Python and then have Python create the Excel. That's the way I've implemented this.

[AlanC]

VBS is very deprecated. Do not use it for anything. PowerShell is a much better option if you go that route and it is easier to use. A custom task is the best but it requires some C# coding. 

Too often, the idea is to do everything in SAS. As a Microsoft product, you can do much, much more in C# with Excel than you can in SAS. SAS is great at doing the ETL but then let the base technologies take over.

If you want 100% control for Excel, consider the custom task. If you only need some level of control, PowerShell can afford that. C# sits on .NET, EG is coded in .NET, and PowerShell is .NET: VBS/VBA/etc. are not. VBA and VBS were deprecated over a decade ago. 

I am like the bearer of bad news on this thread: sorry. I have built numerous custom tasks in EG and am converting 42K lines of VBA code to C# right now. The custom task is actually not bad just read about interfaces and how they work. Create a WPF project in Visual Studio (free) and implement the SAS interfaces. From there, create a simple app with as many dropdowns, lists, comboboxes, etc. that you wish. Visual Studio will actually implement the whole interface for you so there is no need to dig around looking for how to do it.