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
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
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 ');
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.