ods tagsets.excelxp proc report datetime9. column displays as text in excel

[RW9]

Can you post the XML file, or at least a snippet - from Notepad, of the offending XML text (with all the tags, before and after for about 5 rows).  I have not heard of this behavior before.

[Kurt_Bremser]

Hmm.

Could you please post the output of PROC CONTENTS for log_table? At least the list of columns?

[Babloo]

Here you go. PROC CONTENTS for log_table

#	Variable	Type	Len	Format
3	Date	Num	8	DATETIME9.
2	Date_TimeStamp1	Num	8	DATETIME21.2
7	Details	Char	200
4	Status	Char	200
1	fname1	Char	200
5	processid1	Char	200
6	userid1	Char	200

[Kurt_Bremser]

Yeah, right. All the time we were discussing date values, when in fact it is a datetime value with a format that is simply too short. 9 characters is just too short for datetime.

Correction: not too short, mmddyy10. is just not suitable for datetime values.

KNOW YOUR DATA.

[Babloo]

What format should I apply to get the correct output in .xml?

In this thread I've mentioned how my variable looks intially.

Thanks for your time so far.

[RW9]

Well, you would be best clarifying in your code.  I would suggest you format your data date as datetime. i.e. not length 9 as that indicates just a date.  Same with the format in the report.  If you want the datetime output then you can try:

  define Date/style(header)=[background=lightgreen] style(column)={tagattr="format:dd/mm/yyyy hh:mm"};

It might work but Excel doesn't have many built in formats.  It would be easier to split out date and time into separate variables, and outputting to separate columns.

[Peter_C]

SAS produces those stars when a date or time value won't fit the format width or the format is unsuitable.

Change datetime9. to datetime19.

And if it is a datetime value in SAS and you want to output from sas  just a date   use format

f=DTMMDDYY. and not

f=MMDDYY.

Using a date format for a datetime value is a sure way to produce those stars!

Message was edited by: Peter Crawford I rush too much! If format DTMMDDYY hasnt been created yet rather than create your own version with proc format, try with format f=dtdate9. This one has been around since SAS8 (irrc)

[Babloo]

Thanks for your tips Peter.

I tried with both the formats in my code in which only dtdate9. format is seems to be valid.

I modified datetime9. to dtdate9. , but still 'date' variable appears to be in string.

[Tom]

So if you want to write your DATETIME values and only include the date part you can use a format that supports that.

SAS(R) 9.4 Formats and Informats: Reference

Or you could first convert your values into DATE values and then you will have many more formats available.

I would recommend E8601DN as it will print YYYY-MM-DD and even Excel should be able to tell that is a date.

[Babloo]

I formatted the date variable as E8601DN (instead of datetime9.) in datastep and then modified the following statement (I remove {tagattr}) in proc report before submitting the code.

define Date/style(header)=[background=lightgreen]display;

However, date variable is still appears as string in .xml. I think we need to apply some formats in the define statement to make life easier.

[Cynthia_sas]

Hi:

  When I use the code shown in the attached screen shot, in SAS 9.4, without any other use of TAGATTR or any style overrides, I do see that SAS has sent the correct format from SAS to Excel when the XML file is opened by Excel.

cynthia

[BrunoMueller]

To display a date or time in Excel using the ExcelXP tagset you need to have a column that is formated with the E8601DT. format. This will allow you to use the TAGATTR otion to specify how you want to display a date or time in Excel. The sample below shows different data types and how to format them (or some default formatting).

data have;
  attrib
    someText format=$32.
    someDate format=ddmmyyp10.
    someDateXLS format=E8601DT.
    someTime format=time8.
    someTimeXLS format=E8601DT.
    someNumber format=comma12.2
    someCurrency format=dollar14.2
    somePct format=nlpct8.2
  ;

  do i = 1 to 20;
    someText = catx("_", i, put(i, roman.));

    someDate = today() - 100 + i;
    someDateXLS = dhms(someDate, 0, 0, 0);
    
    someTime = time() - i;
    someTimeXLS = dhms(someDate, 0, 0, someTime);

    someNumber = (ranuni(i) * 5000) + 1000;
    someCurrency = (ranuni(i*123) * 10000) + 1000;
    somePct = ranuni(0);
    output;
  end;
  drop i;
run;

ods tagsets.ExcelXP file="c:/temp/value_format.xml"
  style=analysis
  options(
    embedded_titles='yes'
    embedded_footnotes='yes'
  )
;

title "SAS -> Excelxp";
proc report data=have;
  define someDateXLS / 
/*    style(column) = {tagattr='TYPE:DateTime format:[$-F800]DDDD, MMMM DD, YYYY'}*/
    style(column) = {tagattr='TYPE:DateTime format:dd.mm.yyyy'}
  ;
  define sometimeXLS / 
    style(column) = {tagattr="TYPE:DateTime format:hh:mm:ss;@"}
  ;

  define someNumber /
      style(column) = {tagattr="Format:#,##0"}

  ;

run;
title;

ods tagsets.ExcelXP close;

I just noticed, that the code formatting "eats up"  part of the text, see the screenshot below: