SAS date to excel problem

[toffee]

My excel file read source directly from SAS dataset. When i refresh the excel, the SAS date (01Jan2010) become 18263 in excel, 01Feb2010 become 18294...etc. Any chance of getting the same SAS date in excel?

Anyone can help?

[Cynthia_sas]

Hi:
It almost sounds as if your dataset does not have a SAS format associated with the date field. I believe this is the only way that Excel knows a number is a date. If you do a PROC CONTENTS on the SAS dataset:
[pre]
proc contents data=somelib.somedata;
title 'what is format of date variable';
run;
[/pre]

Next, how are you creating the Excel file from a SAS dataset??? PROC EXPORT, the SAS Excel Libname Engine? ODS CSV?? ODS HTML?? ODS MSOFFICE2K?? ODS TAGSETS.EXCELXP???

If your dataset does NOT have a date format associated with the date, then I do not believe that Excel does the appropriate conversion.

The "fix" for this is to 1) associate the appropriate format with the date variable and/or 2) send a Microsoft format from SAS to Excel (only possible with ODS techniques -- ODS HTML, ODS MSOFFICE2K and ODS TAGSETS.EXCELXP.

cynthia

[toffee]

My SAS dataset is in Date9. format (01JAN2009).

I am using excel SAS add in; reading the SAS dataset to excel.....Any chance of converting the 18263 to 01Jan2010?

[Cynthia_sas]

That is very odd. I have not seen that type of behavior with the SAS Add-in for Microsoft office. You might want to check with your SAS administrator to make sure that the format is defined in the metadata or in the information map or in the datasource.

You may also want to open a track with Tech Support-- as I said when I open a SAS dataset into a spreadsheet using the Add-in, I see the date value.

Are you by any chance running a task or wizard on the data and the unformatted date is in the results???

Cynthia

[ErikT]

Even if the date is not formatting well, you can still work with it. In Excel dates are numeric values, just as in SAS. The main difference is that day 0 is different.

If you have a SAS date, as in your case 18263 (1 jan 2010), the equivalent Excel date is 40179. So you have to add 21916

See also my SUGI paper "Dating SAS and Excel", presented at SUGI 29 (2004). You can download it from my web site: www.synchrona.nl. Go to SAS Consultancy in the main menu and choose SUGI 29

[Cynthia_sas]

Hi:
If the variable has a SAS date format assigned, then the SAS Add-in should make the appropriate conversion. If the SAS Add-in is NOT making the appropriate conversion, when the variable DOES have a SAS date format assigned, this would seem to me to be an issue for Tech Support.

To open a track with Tech Support, go to:
http://support.sas.com/ctx/supportform/createForm

cynthia

[GeoffNess]

I've struck this problem before when opening SAS datasets into pivot tables using AMO, where the dataset is accessed from the local filesystem as opposed to a SAS server. This is only an issue when opening the dataset into a pivot table, whereas opening it directly onto a worksheet will display dates correctly.

If this is the issue for the OP, it appears that the only way around this is to place the dataset on the SAS server, and once this is done the SAS date formats will be read correctly into the pivot cache.

[GriffT]

I recently ran into this problem attempting to use the ODS TAGSETS.EXCELXP in a PROC REPORT, the resulting output in .xml would open fine in Excel however the date fields would come through as TEXT even after applying the {TAGATTR='format:m/d/yyyy'} as a STYLE. My solution was two part; first create a new variable adding 21916 days to the original varialbe and secondly not applying a SAS format to the new variable. Now the PROC REPORT using ODS TAGSETS.EXCELXP results in an XML file that opens in Excel and the date fields are recognized by Excel as date and not as text.

Reference for the 21916 days came from this SUGI paper: http://www2.sas.com/proceedings/sugi29/068-29.pdf

[irFrankly]

I found this by far the most simple and effective solution