Excel XP and Proc Report

[abhi19]

    Hi Everyone,

I am trying to create a report in xml using the excel xp tagsets and proc report. One of the column that I need to display is a date in the format:mmddyy10. 

The following is my define statement in proc report:

define Open_Date /format=date9. 'Open Date'

I am using auto-filters for the xml output. Now when the xml output is created the Open Date column shows the format as Text Filters. What I need is instead a Date Filter so that I can filter it according to year.

I need something like this:

How can I do this..?

[Cynthia_sas · Accepted solution]

Hi:

  You did not show your ALL your code, so this is only a guess. I suspect what is happening is that your SAS formats are being ignored (or turned into "General" format by Excel). This is known Excel behavior. Excel decides what format it should use for your column, based on Excel defaults and not necessarily what you set in SAS. This is how leading zeroes, decimal places, date formats, percents, column widths, etc, etc get "messed up" when you open your HTML or XML file with Excel.

  If you are using HTML and ODS, then you need to pass a Microsoft number format (mso-number-format) from SAS to Excel using the HTMLSTYLE attribute. If you are using TAGSETS.EXCELXP and ODS to make an XML 2003 file, then you need to pass your Microsoft format (your MICROSOFT format) from SAS to Excel using the TAGATTR style attribute.

  There are 2 Tech Support notes that are relevant:

http://support.sas.com/kb/38/105.html

http://support.sas.com/kb/38/143.html

  By default, if you send NO other controls, I believe that TAGSETS.EXCELXP converts your date to a text string and sends it to Excel. In particular, when you use PROC REPORT, if you have your DATE variable as an ORDER or GROUP item, then even with TAGATTR, you still see an autofilter of Text Filter in Excel. But, if you have a different usage (such as DISPLAY), then you should see Date Filter, as shown in the attached screenshot. The code I used was modified from the Tech Support note about dates above. Note the autofilter difference for join_iso vs dob_iso in the screenshot.

cynthia

Also, here are some papers on the subject:

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

http://support.sas.com/resources/papers/proceedings10/031-2010.pdf

data bday;

  ** make "regular dates" and also, make

     ISO format versions of the dates;

  infile datalines;

  input name $ dob : mmddyy8. join_date : mmddyy8.;

  join_iso = dhms(join_date,0,0,0);

  dob_iso = dhms(dob,0,0,0);

return;

datalines;

alan 11/29/84 06/17/13

barb 01/14/78 06/14/13

carl 08/23/92 06/17/13

dave 05/03/62 06/14/13

;

run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\testdate.xml'

     options(autofilter='on' sheet_name='One') style=sasweb;

proc report data=bday nowd;

  title '1) Use ISO Date and TAGATTR Excel';

  column join_date join_iso name dob dob_iso;

  define join_date / f=date9. order;

  define join_iso / order f=is8601dt.

      style(column)={tagattr='TYPE:DateTime format:mm/dd/yy;@'};

  define name / order;

  define dob /display f=date9.;

  define dob_iso / display f=is8601dt.

      style(column)={tagattr='TYPE:DateTime format:mm/dd/yy;@'};

run;

ods tagsets.excelxp close;

[abhi19]

Thanks a lot Cynthia. It worked well.