topic Re: Office Analytics - Pivot Table Output and Unsuccessful trying to Group Date Field in Microsoft Integration with SAS

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

BillB — Fri, 26 Jul 2013 15:39:49 GMT

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?

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

Reeza — Fri, 26 Jul 2013 15:46:25 GMT

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.

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

BillB — Fri, 26 Jul 2013 15:50:24 GMT

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

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

Reeza — Fri, 26 Jul 2013 16:00:25 GMT

When you did what?

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

BillB — Mon, 29 Jul 2013 12:12:55 GMT

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.

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

Reeza — Mon, 29 Jul 2013 16:13:25 GMT

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.

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

BillB — Mon, 29 Jul 2013 20:40:03 GMT

Didn't work.

Any other suggestions?

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

Reeza — Mon, 29 Jul 2013 20:48:00 GMT

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.

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

BillB — Mon, 29 Jul 2013 20:53:57 GMT

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.

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

Reeza — Mon, 29 Jul 2013 20:58:25 GMT

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?