topic Excel SAS addin - date format won't change in SAS Programming

Excel SAS addin - date format won't change

sam5 — Fri, 14 Sep 2018 09:20:28 GMT

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.

Re: Excel SAS addin - date format won't change

ChrisNZ — Sun, 16 Sep 2018 03:46:21 GMT

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.

Re: Excel SAS addin - date format won't change

sam5 — Mon, 17 Sep 2018 09:33:49 GMT

Thanks for replying.

Would mising values be an example of an invalid value?

Re: Excel SAS addin - date format won't change

ChrisNZ — Tue, 18 Sep 2018 00:15:54 GMT

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.

Re: Excel SAS addin - date format won't change

sam5 — Tue, 18 Sep 2018 09:48:01 GMT

Thanks for your help. Once I changed a missing value to 0 I was able to format the dates without any issue.