ODS tagsets.excel xp Format datetime value to HHMM in excel output

[lbatwood]

Hello,

I have a SAS column (delay_hrs) formatted as HH:MM.  When I use ODS tagsets.excel XP I can not format and bring up in Excel in the HH:MM format.  When I change the format in excel to HH:MM it does not work either.  In Excel it sums everything as zero and only counts the values.

Here is the code

ODS TAGSETS.EXCELXP FILE=_WEBOUT STYLE=STYLES.TEST OPTIONS(index='on' ORIENTATION='landscape' sheet_name="details"
absolute_column_width='8' embedded_titles='yes' /*sheet_interval='none'*/);

TITLE;
title1 justify=center "%sysfunc(putn('&start_morning_run'd, MMDDYY8.))- %sysfunc(putn('&end_morning_run'd, MMDDYY8.))";
title2 justify=center "Daily Turn Report";

PROC REPORT DATA=work.QUERY_FOR_APPEND_TABLE_000B NOWD;

column cc cc_sn turn_dt turn_ident unit1 turn_dt1 turn_ident1 wght delay_hrs Desc delay_typ
desc_2 mach_strt_dttm mach_fnsh_dttm;

define cc / group noprint;
define cc_sn / group noprint;
define turn_dt / group noprint;
define turn_ident / group noprint;

define unit1 / display 'Unit' style=[font_weight=bold];
define turn_dt1 / display 'Turn Dt' style=[font_weight=bold];
define turn_ident1 / display 'Turn' style=[font_weight=bold];
define wght / display 'Fin Tons' format=comma8.1;
define delay_hrs / display 'Delay Hrs' format=hhmm. ;
define Desc / display 'Desc' style=[font_weight=bold];
define delay_typ / display 'Delay Typ';
define desc_2 / display 'Detl Cmts' format=$1024.;
define mach_strt_dttm / display 'Delay Strt';
define mach_fnsh_dttm / display 'Delay Fnsh';

compute after cc_sn;

/*Line ' ';*/
endcomp;

QUIT;
run;

[Cynthia_sas]

Hi:

  When I test with some fake data, both ODS TAGSETS.EXCELXP and ODS EXCEL create output files that show the correct time and date/time formats when opened in Excel, as shown below:

  I took all the style defaults for both ODS EXCEL and ODS TAGSETS.EXCELXP, so that is the reason for the different styles being used. But the numbers in the cells in each sheet are correct.

  So I am wondering whether there is something in your data that is not defined correctly when the data is created. Since you did not provide data, it's hard to make a constructive suggestion.

  But since you said that your delay_hrs variable was formatted with HHMM in the data, that implies to me that the variable is a numeric time variable. Just to double check that the formats were used in Excel, I also created a date/time variable in my test that also worked, so there must be something else happening with your data.

Cynthia

[lbatwood]

Hello,

If I format the column as format=datetime10. it shows up as 01JAN60:03 in excel.  If it was HHMM format it would be 3:04.

I hope this helps, Thanks in advance

[Cynthia_sas]

Hi:
Jan 1, 1960 is the 0 date in SAS dates in SAS are stored as numbers internally with 0 as the "middle" of a timeline, like this:

  So a negative number in the data column represents a date BEFORE Jan 1, 1960 and a positive number represents a date AFTER Jan 1, 1960. Time values are numbers of seconds from midnight. From the doc: "A SAS time value is the number of seconds since midnight of the current day. SAS time values are between 0 and 86400. A SAS datetime value is the number of seconds between January 1, 1960 and a specific hour, minute, and second of a specific date."

So, I suspect that something is wrong with your date variable in YOUR data. The information you've posted implies that if your date was NOT supposed to represent Jan 1, 1960, then something is wrong in the data. As you can see in my example, when the data is created correctly as numeric time and datetime variables, the internal SAS values ARE displayed correctly.
Cynthia

[SASKiwi]

You could try telling Excel to use a custom format:

define delay_hrs / display 'Delay Hrs' format=hhmm. style=[tagattr="format:h:mm"];