topic Re: Datetime format into Excel in SAS Programming

Datetime format into Excel

kgot — Wed, 07 Jan 2015 19:32:35 GMT

SAS newbie here--I am putting together an Excel spreadsheet via the ods tagsets.excelxp method. I have been searching online, but I have not yet found a way to display a datetime in a way that Excel would recognize as a datetime. My Excel spreadsheet currently displays (for example) 06JAN2015:07:39:37, when ideally, I would like it to read 01/06/2015 07:39 (24-hr format). Is there a datetime format that would allow me to do this? If not, what is the best way to go about this? I stumbled upon datepart and timepart functions, so the best I can think of for now is to concatenate those together, but I would be interested to know if there are other methods out there. Thank you!

Re: Datetime format into Excel

Tom — Wed, 07 Jan 2015 20:37:03 GMT

Here is an old note explaining about using the TAGATTR feature to tell Excel how to format the dates.

38143 - Apply date formats with the ExcelXP destination

Re: Datetime format into Excel

kgot — Thu, 08 Jan 2015 21:56:37 GMT

Thank you, Tom--that worked perfectly for my proc print statements. I am only having difficulty with a proc report--it does not seem that tagattr is an option for that--is there a way to do the same thing in a proc report?

Thanks again!

Re: Datetime format into Excel

ballardw — Thu, 08 Jan 2015 22:41:35 GMT

Here's an example of proc report and tagattr.

proc report data=sashelp.shoes(obs=10) nowd ;

title 'Using tagattr and TAGSETS.EXCELXP';

title2 'Send a Formula for a Column';

column region product sales profit;

define region/display;

define product /display;

define sales/ sum 'Sales'

style(column)={tagattr="$###,##0.00"};

define profit / computed "Profit"

style(column)=

{tagattr='Formula:RC[-1]*0.9'};

compute profit;

** profit is not in sashelp.shoes;

** so make an empty column for the formula;

profit = 0;

endcomp;

run;

Re: Datetime format into Excel

kgot — Fri, 09 Jan 2015 12:54:00 GMT

Got it--that worked! I was trying to use just "style" instead of "style(column)"--once I changed that, it worked perfectly.

Thank you to both of you--I really appreciate it!