Office Analytics - Pivot Table Output and Unsuccessful trying to Group Date Field

[BillB]

Whe  I access a data set via Office Analytics and import it via Pivot table all the fiels seem ok until I try to Group the Date field.

Normally when you click Group in Excel Pivot Table on a date fiels it would give you a set of choices to Group (Day, Month, Auarter, Year); however, when I try this in the SAS dataset Pivot table out put it only shows numbers e.g. 4198, 4199.

Any ideas how to correct this so I can group it in Office Analytics as Months?

[Reeza]

Basically Excel is seeing a SAS number, which is a date. You need to change the date to a format that excel will understand.

So, you can try converting the date to a character field that looks like a date that Excel might read as a date.

[BillB]

Thank's Reeza but when I did that the dates changed to numbers e. g. 4091, 4092, 4093 etc..

[Reeza]

When you did what?

[BillB]

Reeza;

Per what your previously sent (See below)

"

Basically Excel is seeing a SAS number, which is a date. You need to change the date to a format that excel will understand.

So, you can try converting the date to a character field that looks like a date that Excel might read as a date".

I changed the date format in the Excel output to text.

[Reeza]

If its in Excel, go to the format cells column and format as a date.

If you're in SAS then format the date as character before exporting to excel.

[BillB]

Didn't work.

Any other suggestions?

[Reeza]

It worked for me and your response is vague, so I don't know what you tried and what didn't work.

If you want more help provide:

1. What your data looks like in SAS

2. What it looks like in Excel

3. What you'd expect in Excel

IMO this is an excel issue, so you can consider posting in an Excel forum as well, or contact Tech Support.

[BillB]

Reeza;

Here are the steps I followed:

1> Opened Excel and Selected the SAS menu item at the top of the menu bar.

2> I selected the data set.

3> I selected the Import to Pivot table.

4> The data was imported into Excel.

5> I selected the dat field and

     a. First tried formatting as a Date type. Then tried Grouping as Month. The only thing I saw was a dialog box with four digit numberss.

     b. Second tried formatting as Character.

No luck either way.

[Reeza]

You need to talk to whoever developed the data, its not coming in to Excel properly. They need to make sure it comes in as a date format that Excel can recognize.

You could calculate a date from that if you wanted in a new column, but that isn't an efficient solution IMO.

In SAS world, 4198 corresponds to 30Jun1971 does that make sense for your data?