Is there a way to get SAS dates to come into Excel as a date? When opening a local SAS Data Source (sas dataset) a message appeared that the dates could not be read. The resulting worksheet had the numeric value for the SAS date instead of a date.
If have tried to search for this in support.sas.com with no luck, however have seen many errors about opening files from a local computer. Could this be another issue? Is there a better way to open local files without having to register everything possible into metadata?
The answer to this question will vary, depending on whether you are using PROC EXPORT to get your SAS dates into Excel or using ODS to get your dates into Excel.
For example, by searching at support.sas.com, on the string
Proc Export SAS dates
I found this note that discusses how to fix the problem using PROC EXPORT: http://support.sas.com/kb/16/612.html
If you are creating files with ODS HTML, then there is a section in this document http://support.sas.com/rnd/base/ods/templateFAQ/Excel1.pdf
that discusses using the HTMLSTYLE attribute with ODS HTML to send a Microsoft Format from SAS to Excel. In addition, if you are using ODS CSV, there is an example of how to have have cell formats used in the same paper.
Actually, it was using the Add-In for MS Office. Within Excel, the user selected the SAS button, then Open Data Source, pointed to a SAS dataset on a mapped drive. Then the message came up (sorry I did not get a screen shot). When the data was populated in Excel it was in the format of the numeric SAS date instead of a formatted date. When bringing in data from the Server (metadata library) this is not an issue.
Are you saying that SAS uses the import procedure behind the scenes when using the SAS button and opening a data source?
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.