topic excel getting text date and making it a number in ODS and Base Reporting

excel getting text date and making it a number

wkossack_nspirehealth_com — Fri, 21 Jun 2013 15:40:58 GMT

I have a proc print that is creating a spreadsheet in excel

it seems to work except for a date which is stored as a text field (that is the way the requirements are written) is being output as a number to excel which thus displays it as a 1 in the cell but as the correct date in the formula bar

Re: excel getting text date and making it a number

Cynthia_sas — Fri, 21 Jun 2013 17:11:23 GMT

Hi,

How you are creating the file for Excel is crucial information. Are you using PROC EXPORT, or the LIBNAME engine? Are you using ODS HTML, ODS CSV or ODS TAGSETS.EXCELXP? You have not posted any sample code. It appears that you have not searched support.sas.com or even this forum. This question has been asked and answered many times, several of them by me, if you are using ODS techniques to make your file for Excel.

If you searched support.sas.com, you should have found this (specifically about dates in Excel). Excel has its own way of treating your information, that's why there are issues with leading zeroes, percents signs, decimal places and dates.

38105 - How can I control the formatting of my output when exporting output to Excel using ODS?

38143 - Apply date formats with the ExcelXP destination

cynthia

Re: excel getting text date and making it a number

PGStats — Fri, 21 Jun 2013 17:13:50 GMT

I don't quite understand your situation, but seems like you could use the fact that any field starting with an apostrophe will remain a text cell and Excel will not display the apostrophe. - PG

Re: excel getting text date and making it a number

wkossack_nspirehealth_com — Fri, 21 Jun 2013 17:49:20 GMT

the date (birthdate) is a text field in SAS. I think it is that way because that is in the requirements

when I try using

proc print....

var birth /style={htmlstyle="mso-number-format:\@"};

I get

Warning: Yes/No options only take, yes, no, on, or off as valid values

WARNING: In Event: style_over_ride, Invalid Expression:

ERROR: WHERE clause operator requires numeric variables.

WARNING: In Event: style_over_ride, Invalid Expression:

ERROR: WHERE clause operator requires numeric variables.

when I stick

ods path work.templat(update) sashelp.tmplmst(read);

it works but then the ods output options are ignored such as column width

these are

ods tagsets.ExcelXP options(embedded_footnotes='yes' embedded_titles='yes'

suppress_bylines='yes' orientation='landscape'

sheet_interval='bygroup' sheet_label=' '

width_fudge='.7' width_points='1'

absolute_column_width='125,68.5,68.5,68.5,68.5,68.5,68.5,68.5'

center_horizontal ='yes ');

Re: excel getting text date and making it a number

Cynthia_sas — Fri, 21 Jun 2013 23:09:57 GMT

Hi:

ODS TAGSETS.EXCELXP is not HTML. Using HTMLSTYLE is *only* appropriate with HTML-based methods of creating output for Excel. When you use ODS TAGSETS.EXCELXP, you need to use the TAGATTR= style attribute method of describing the field as text and/or send a Microsoft format from SAS to Excel using TAGATTR.

I have posted a link to my paper on using HTMLSTYLE (for HTML) and TAGATTR (for EXCELXP) several times. Here it is again.

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

If you search the forum postings or support.sas.com for TAGATTR=, you should find even more hits.

cynthia