Can "." be replaced from PROC REPORT into an Excel file using ODS ExcelXP tagset?

Reply
Occasional Contributor
Posts: 7

Can "." be replaced from PROC REPORT into an Excel file using ODS ExcelXP tagset?

This is a total rookie question I'm sure.

I'm using a basic PROC REPORT and I'd like to not have "." appearing for blank dates in the Excel file that is generated.  I've tried using the OPTIONS MISSING="" statement but to no avail.

Thanks.

PROC Star
Posts: 1,322

Re: Can "." be replaced from PROC REPORT into an Excel file using ODS ExcelXP tagset?

Posted in reply to old_man_hank

Hmmm, setting option missing="" works for me.

You might try adding a blank in there instead of null (i.e. missing=" "), but both ways works for me.

Occasional Contributor
Posts: 7

Re: Can "." be replaced from PROC REPORT into an Excel file using ODS ExcelXP tagset?

Thanks Quentin.  Does it matter if I am using some style attributes in the PROC REPORT, such as the below:

     format testdate is8601dt.;

     define    testdate     /.     display style(column)={tagattr="typeSmiley Very HappyateTime format: mm/dd/yyyy;@"};

PROC Star
Posts: 1,322

Re: Can "." be replaced from PROC REPORT into an Excel file using ODS ExcelXP tagset?

Posted in reply to old_man_hank

Hi,

Looks to me like it's actually your format that is the deciding factor.  Sample code below.

If I change the format to mmddyy10, I get the expected blank.  But with is8601dt I see the dot.

data a;
  testdate=.;
  format testdate is8601dt.; 
  *format testdate mmddyy10.;
run;

options missing=' ';

ods tagsets.excelxp file="d:\junk\testme.xml";
proc report data=a nowd;

  columns testdate
  define testdate      /display style(column)={tagattr="type:DateTime format: mm/dd/yyyy;@"}   ; run; ods _all_ close;

I try to avoid delivering results in Excel, because I hate Excel.  But when I have to do it, I always find this paper by Cynthia Zender very helpful in guiding me through tagattr:

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

It has a section on dealing with dates.

Occasional Contributor
Posts: 7

Re: Can "." be replaced from PROC REPORT into an Excel file using ODS ExcelXP tagset?

Thanks Quentin, I'll have to rethink how I output my dates.  On the one hand I like the output with is8601dt because it makes Excel treat it as a "real" date (can be filtered by year, month, etc.) but I'm stuck with dots for missing dates.  On the other hand I can use another format and not get dots but then Excel treats the date as text and all that good "real" date functionality is lost.

Ask a Question
Discussion stats
  • 4 replies
  • 231 views
  • 3 likes
  • 2 in conversation