Hi:
No, that's not what I'm saying at all. If you were using BASE SAS (which you're not), then these suggestions outlined above would be the methods you would use to get a date recognized by Excel. Once you clarified that you were using the SAS Add-in for Microsoft Office then NOTHING of what I wrote applies to your question.
You are NOT using BASE SAS -- you are using the BI Platform and the SAS Add-in for Microsoft Office. The method that the Add-in uses to get data is more like this:
The Add-in for Microsoft Office opens a pipeline between the Office product (Excel) and the data source (SAS) -- going through the METADATA for information about permissions, where the data lives, etc.
If the data source is a SAS data source -- either a SAS file or an Information Map, then intelligence is built into the access method to send the data down that pipeline. For non-SAS data sources (like Oracle or DB2 tables), then the metadata access method is used to get the data source and send it down the pipeline.
If you point to a data source and perform an analysis using a Task or Wizard, then analytic results come down the pipeline.
If you run a stored process, then the stored process results come down the pipeline.
It was my understanding that as long as you had a regular SAS date format assigned to the variable in the data source, that the Add-in would use that format.
SAS and Excel have slightly different ways to store dates. SAS stores them internally as the number of days since Jan 1, 1960. Microsoft stores them internally as the number of days since Jan 1, 1900. SAS dates can go back to the 1500's. Microsoft dates can only go back to Jan 1, 1900.
(Microsoft's date approach is explained here:
http://www.cpearson.com/excel/datetime.htm)
When you open a data source into Excel using the SAS Add-in, you should see a window pop up that has the list of variables to choose. (This is the Modify Data Source window and it has 4 tabs -- Variables, Filter, Sort and Output Location) In the list of variables on the first tab, the icons denote what kind of variable you are dealing with. The icons are:
---blue circle with 123 inside the circle -- this is a numeric variable
---red pyramid with the letter A inside the pyramid -- this is a character variable
---multi-color calendar with one blue square highlighted -- this is a numeric variable that SAS recognizes as being a date
---currency symbols -- this is a numeric variable with a currency format applied SAS recognized this variable as having to do with money/currency.
When I use the SAS Add-in to open a data source, as long as I see the calendar icon, my dates come into Excel (from SAS) correctly. If you see the calendar icon in your window when you open the data source but the date is not being recognized by Excel, then you have a problem for Tech Support.
If you see the variable in window, but it does not have a calendar icon next to it, then you may know that it's a date variable and the variable name may contain the word 'Date' -- but the lack of a calendar icon means that the METADATA does not know that your variable is a date variable. If the METADATA does not know that your variable is a date variable then there's no possible way for the Add-in to tell Excel that your variable is a date variable. In order to assign a date format to your data source in such a way that the format is recognized in the metadata -- you probably should contact Tech Support for more help, since the method you use to assign the format will depend on whether you are just dealing with a data source or whether you could possibly be dealling with an Information Map.
It would probably help Tech Support if you could grab the text of the error message because it's possible that you do have a format assigned to the date variable, but either it's not one of the standard date formats or you have discovered something that needs to be reported to the developers.
Now that you have clarified your question, your best bet for help is to contact Tech Support.
cynthia
