DATA Step, Macro, Functions and more

Datetime format into Excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Datetime format into Excel

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!


Accepted Solutions
Solution
‎01-07-2015 03:37 PM
Super User
Super User
Posts: 7,060

Re: Datetime format into Excel

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

View solution in original post


All Replies
Solution
‎01-07-2015 03:37 PM
Super User
Super User
Posts: 7,060

Re: Datetime format into Excel

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

Occasional Contributor
Posts: 11

Re: Datetime format into Excel

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!

Super User
Posts: 11,343

Re: Datetime format into Excel

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;

Occasional Contributor
Posts: 11

Re: Datetime format into Excel

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!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 363 views
  • 3 likes
  • 3 in conversation