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,231

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

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,231

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

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
  • 225 views
  • 3 likes
  • 2 in conversation