Help using Base SAS procedures

Excel XP and Proc Report

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Excel XP and Proc Report

    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.

open date.JPG

I need something like this:

date.bmp

How can I do this..?


Accepted Solutions
Solution
‎06-15-2013 11:35 AM
SAS Super FREQ
Posts: 8,743

Re: Excel XP and Proc Report

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='TYPESmiley Very HappyateTime format:mm/dd/yy;@'};

  define name / order;

  define dob /display f=date9.;

  define dob_iso / display f=is8601dt.

      style(column)={tagattr='TYPESmiley Very HappyateTime format:mm/dd/yy;@'};

run;

  

ods tagsets.excelxp close;


excel_filter_date.png

View solution in original post


All Replies
Solution
‎06-15-2013 11:35 AM
SAS Super FREQ
Posts: 8,743

Re: Excel XP and Proc Report

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='TYPESmiley Very HappyateTime format:mm/dd/yy;@'};

  define name / order;

  define dob /display f=date9.;

  define dob_iso / display f=is8601dt.

      style(column)={tagattr='TYPESmiley Very HappyateTime format:mm/dd/yy;@'};

run;

  

ods tagsets.excelxp close;


excel_filter_date.png
Occasional Contributor
Posts: 5

Re: Excel XP and Proc Report

Thanks a lot Cynthia. It worked well. Smiley Happy

☑ This topic is SOLVED.

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

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