topic Reply from Tech support in ODS and Base Reporting

ODS Excel data validation

AndersVagiste — Wed, 16 Aug 2017 13:09:05 GMT

Hello!

I would like to create a drop down for data validation in Excel with Proc Report , something like the picture below, how do I do that?

SAS 9.4, ODS Excel,

Re: ODS Excel data validation

Reeza — Wed, 16 Aug 2017 15:14:20 GMT

As far as I know, ODS EXCEL doesn't have an option for something like that yet. You may be able to use DDE and/or VBA to create something similar. Depending on what you're doing there may be other options. If you provide more details we can advise you further.

Re: ODS Excel data validation

AndersVagiste — Thu, 17 Aug 2017 08:03:01 GMT

Thank You very much for Your answer and interest in this.
I think there is a solution for this but I don't know how it works but I found this in a paper, and here is something that points to validation in Excel from SAS
https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf

[cid:image001.png@01D3173F.E8A9FFB0]

Re: ODS Excel data validation

AndersVagiste — Thu, 17 Aug 2017 08:15:13 GMT

It's page 31 I mean.

Re: ODS Excel data validation

SuzanneDorinski — Fri, 18 Aug 2017 15:16:41 GMT

The paper is by Chevell Parker. Page 31 mentions that he's using a modification of the ExcelXP tagset, and that an example of the tagset is available in the Resources section of the paper. I assume that's the downloadable file. I haven't used the ExcelXP tagset in a while, so I didn't read all the way through his paper. But now I'm curious about this, so I'll try it out.

Re: ODS Excel data validation

Reeza — Fri, 18 Aug 2017 16:29:39 GMT

That's not ODS Excel - you'll get an XML file instead, you can convert it to XLSX after.

Have you gotten his sample to run? That's where I'd probably start, the code is there to test it.

Reply from Tech support

AndersVagiste — Thu, 24 Aug 2017 09:53:25 GMT

There is no support for creating "Data Validation" cells in Excel but there are the wishes of many users.
There are, however, alternatives, just as complicated.

Det finns inget support för att skapa "Data Validation" celler i Excel men det finns som önskemål av många användare.

Det finns däremot alternativ, rätt så krångliga.

A modified version of EXCELXP

https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf

Use VBA code and call SAS within Excel.

http://support.sas.com/resources/papers/proceedings13/203-2013.pdf

Det finns inget support för att skapa "Data Validation" celler i Excel men det finns som önskemål av många användare.

Det finns däremot alternativ, rätt så krångliga.