Hi,
I have an excel 2016 spreadsheet which creates a pivot table using the SAS addin.
There is a problem with the way one of the dates is formatted. Even though the table on SAS has the date9. format, the date exports as a number and not the date itself. When I try to change the format on excel to mmm-yy the format will only change if the date is in the row, column or value sections of the pivot table. However, I want the date to be a filter and changing the format when the date is a filter does nothing.
On top of this it works as expected for another date in the table.
Not too sure how excel see these.
The best way to go the the bottom of this is to lowly reduce the amount of data until you isolate the culprit.
Or conversely, start with one working row, and add data until you isolate the culprit.
It's hard to debug such things remotely, but several factors in your description give a hint that the misbehaviour might be due to a bad (i.e. invalid) value for that variable somewhere in the data.
Thanks for replying.
Would mising values be an example of an invalid value?
Not too sure how excel see these.
The best way to go the the bottom of this is to lowly reduce the amount of data until you isolate the culprit.
Or conversely, start with one working row, and add data until you isolate the culprit.
Thanks for your help. Once I changed a missing value to 0 I was able to format the dates without any issue.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.