BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
abhi19
Calcite | Level 5

    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..?

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;


excel_filter_date.png

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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;


excel_filter_date.png
abhi19
Calcite | Level 5

Thanks a lot Cynthia. It worked well. Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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