BookmarkSubscribeRSS Feed
wkossack_nspirehealth_com
Calcite | Level 5

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

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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

wkossack_nspirehealth_com
Calcite | Level 5

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 ');

Cynthia_sas
SAS Super FREQ

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

PGStats
Opal | Level 21

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

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1015 views
  • 0 likes
  • 3 in conversation