The SAS Output Delivery System and reporting techniques

ODS Excel maintaining date time formatting when missing values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

ODS Excel maintaining date time formatting when missing values

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 :-)

 

 

ods excel output formatting issue 06.21.2018.png

 

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:

sas eg version 06.21.2018.png

 


Accepted Solutions
Solution
‎06-27-2018 10:30 AM
Respected Advisor
Posts: 4,791

Re: ODS Excel maintaining date time formatting when missing values

[ Edited ]

@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;

View solution in original post


All Replies
Solution
‎06-27-2018 10:30 AM
Respected Advisor
Posts: 4,791

Re: ODS Excel maintaining date time formatting when missing values

[ Edited ]

@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;
Occasional Contributor
Posts: 6

Re: ODS Excel maintaining date time formatting when missing values

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

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 134 views
  • 0 likes
  • 2 in conversation