topic Re: Field is character but shows up in Excel as scientific in SAS Programming

Field is character but shows up in Excel as scientific

cperrelli — Fri, 25 Jan 2019 22:46:58 GMT

Hi,

I have a database column that is defined as

VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC

When I use ODS and create an xls file the field shows up a scientific numbers in Excel. If I change the column format manually to number this solves this issue. The thing is this file is going to be emailed from LINUX without any manual intervention. What can I do to show the field as numeric in Excel as it seems it's translating the character field. When I did a proc contents and proc print on the file it's defined as char(21) and the numbers appear correctly. What do I need to do to make excel recognize this field as either the character that It is defined as or change it to number? I am not able to attach the list file due to security regulations.

Re: Field is character but shows up in Excel as scientific

VDD — Fri, 25 Jan 2019 22:52:39 GMT

define the column as numeric not varchar

when is SAS think as SAS and leave MS behind.

Re: Field is character but shows up in Excel as scientific

Cynthia_sas — Fri, 25 Jan 2019 23:19:46 GMT

Hi:
ODS does not create an XLS file. It creates an XLSX (ODS EXCEL) or an XML file (ODS TAGSETS.EXCELXP). With the value turned into a number, Excel may still not treat the number correctly.

Typically, which ever one you use, you may have to send a Microsoft format from SAS to Excel.

Cynthia

Re: Field is character but shows up in Excel as scientific

cperrelli — Tue, 26 Feb 2019 13:49:01 GMT

Thanks for your reply. I was able to resolve the issue.