ODS Excel maintaining date time formatting when missing values

[jlr337]

Hi!

I am using ODS excel and it is working great except for one formatting issue.  I have date and time fields that format correctly in excel when there is a value, but the formatting is not the same if the cells are blank.  This is causing a problem/extra step for the end user.  The user wants to enter data after review into the empty cells.  Below is an example. The first row had values and exported from sas correctly using proc report/ods excel. I entered the same values in the second row directly in Excel.  The formats should be mmddyy10 and hh:mm for all cells 

Users can modify the cells using number format in excel but I would like to be able to eliminate that step.

The formatting is maintained when using ods tagset.excelxp regardless of value or not but I'm trying to get away from using this because it require either me or the end user to have an added step of re-saving the file as .xlsx since the output is in .xml. (I get warnings when I open & modify an .xml document so I always re-save as .xlsx before sending to users-  I'm sure my end users would be annoyed by all warning messages if it was sent as is)  I'm trying to make the process as efficient, convenient as possible so I'm open to any suggestions 🙂

In my program I have tried to format the variables in the proc sql step as well as in the define column portion of the proc report but neither maintains the formatting. 

Example code:

proc sql;
create table pt_list_dtl as
select

a.HSP_ACCOUNT_ID

,a.pt_name_mrn
,b.cdate format mmddyy10.
,b.ctime format hhmm.
from ...
where...
;
quit;

options missing=' '; 

ods listing close;

ods excel file='...xlsx';

ods excel

options (...);

proc report data=pt_list_dtl;
where pt_name_mrn in (&pat);
column cdate ctime;
define cdate/display 'Date Collected' format=mmddyy10. style(column)={cellwidth=1.5in};
define ctime/display 'Time Collected' format=hhmm. style(column)={cellwidth=2in};
run;

I'm currently using:

[Patrick · Accepted solution]

@jlr337

Below code should apply a constant cell format within the range of your output.

data sample(drop=_:);
  length cdate ctime 8;
  _cdate=today();
  _ctime=time();
  do _i=1 to 5;
    cdate=_cdate+_i;
    ctime=_ctime+50*_i;
    if _i=3 then call missing(cdate, ctime);
    output;
  end;
  stop;
run;

options missing=' ';
ods listing close;
ods excel file='c:\temp\test.xlsx';
ods excel options(index='on' SHEET_NAME='text-string');
proc report data=sample;
  column cdate ctime;
  define cdate/display 'Date Collected' format=mmddyy10. style(column)={cellwidth=1.5in tagattr='format: mm/dd/yyyy'};
  define ctime/display 'Time Collected' format=hhmm.     style(column)={cellwidth=2in tagattr='format: hh:mm'};
run;
ods excel close;
ods listing;

[jlr337]

This is awesome!!  I don't understand yet how it's working but it IS working 🙂  Thank you so much.