topic Re: Any way to change List Report's date output format? in SAS Enterprise Guide

Any way to change List Report's date output format?

boey — Wed, 18 Dec 2013 03:06:25 GMT

I am having a problem of date output format when sending my list report to excel, the date format originally set in SAS EG has changed from DD/MM/YYYY to mixure of DD/MM/YYYY and MM/DD/YYYY. Any solution for this?

Re: Any way to change List Report's date output format?

TomKari — Wed, 18 Dec 2013 10:47:39 GMT

How are you sending it to Excel?

Tom

Re: Any way to change List Report's date output format?

boey — Fri, 27 Dec 2013 09:16:13 GMT

Re: Any way to change List Report's date output format?

Jagadishkatam — Fri, 27 Dec 2013 09:30:13 GMT

Were the dates in numeric format or character format.

if numeric, please check the format of the dates. If the format is fixed across the column then there should not be any problem in exported file.

If the format is not fixed, then the excel generated will have mixed formats. Could you please assign the fixed format to the column of date and check.

Thanks,

Jag

Re: Any way to change List Report's date output format?

NaveenSrinivasan — Fri, 27 Dec 2013 09:41:21 GMT

Hi,

I'd like suggest you to

1. apply your desired format whether dd/mm/yyyy or mm/dd/yyyy by modifying the code using the format statement that should be written to the output datset. Safely, this should resolve.

2. Verify the results by checking with filter.

3. Then export to excel

I am guessing there might be an issue in permanently associating(storing) your desired Date format in the descriptor portion of the output dataset, which you are trying to export to excel.

HTH,

Naveen

Re: Any way to change List Report's date output format?

boey — Fri, 27 Dec 2013 09:46:51 GMT

table on the left side is SAS list report output; table on the right side is exported excel output.

I have already fixed the format in SAS EG with DDMMYYS.10 but when sending to excel, the column shows the mixed format (date and character).

Re: Any way to change List Report's date output format?

Jagadishkatam — Fri, 27 Dec 2013 09:54:10 GMT

Could you please change the format to ddmmyy10. ,

i believe DDMMYYS.10 is not a valid format.

Thanks,

Jag

Re: Any way to change List Report's date output format?

boey — Fri, 27 Dec 2013 10:04:01 GMT

sorry, it is DDMMYYS10., it was just my typing error here.

Re: Any way to change List Report's date output format?

kuridisanjeev — Fri, 27 Dec 2013 10:04:46 GMT

Yes,

as Jag said ,format name should be DDMMYYS10. instead of DDMMYYS.10

Thanks,

Sanjeev.K

Re: Any way to change List Report's date output format?

TomKari — Fri, 27 Dec 2013 17:04:48 GMT

Yes, I've been able to replicate your problem. I'm guessing it's because SAS is sending the formatted value in the "dd/mm/yyyy" format, e.g. 01/02/2014, for Feb 1 2014, to excel. I've bumped into this a few times...any time excel sees "99/99/9999", it assumes mm/dd/yyyy as long as the first number isn't greater than 12.

I'm not sure of your requirements, but I was able to get the transfer to work by putting a format of date9. on my date column in the List Report task. This sends it to excel in an unambiguous 01feb2014 format, which excel recognizes. From there, you can format it in excel.

Tom

Re: Any way to change List Report's date output format?

boey — Tue, 07 Jan 2014 01:04:10 GMT

It helps! Thanks Tom!

Boey