SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Reply
Contributor
Posts: 42

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


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?

Grand Advisor
Posts: 16,316

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

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.

Contributor
Posts: 42

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

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

Grand Advisor
Posts: 16,316

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

When you did what?

Contributor
Posts: 42

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

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.


Grand Advisor
Posts: 16,316

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

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.

Contributor
Posts: 42

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

Didn't work.

Any other suggestions?

Grand Advisor
Posts: 16,316

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

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.

Contributor
Posts: 42

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

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.

Grand Advisor
Posts: 16,316

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

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?

Post a Question
Discussion Stats
  • 9 replies
  • 1585 views
  • 0 likes
  • 2 in conversation