The SAS Output Delivery System and reporting techniques

how to apply the excel format for column headers

Reply
Frequent Contributor
Posts: 111

how to apply the excel format for column headers

Good day all,

i have the dataset of Reports. In dataset like this.

summaryfeb15jan15dec14nov14oct14sep14aug14
count of account value203300450500600650700
sum of account value15000020000030000350000150000260000450000
count of account value500400300200150250350
sum of account value100001400015000250003000045000

50000

i have applied label for columns.

Summary-Detailsfeb-15jan-15dec-14nov-14oct-14sep-14aug-14
count of account value203300450500600650700
sum of account value15000020000030000350000150000260000450000
count of account value500400300200150250350
sum of account value100001400015000250003000045000

50000

when i export into excel using ods html with .xls extension the report will appear but what  happened is that

when we place cursor on feb-15 cell its showing

feb-15     2/15/2015

jan-15     1/5/2015

dec-14     12/14/2015

nov-14     11/14/2015

oct-14     10/14/2015

But i dont want like this at background. actually the date in the monyy5. format in dataset. So for appearance i changed it as feb-15 and jan-15 like this.

what my requirement is i want to show these as labels and at background also showing like this.

feb-15     2/15/2015

jan-15     1/15/2015

dec-14     12/15/2014

nov-14     11/15/2014

oct-14     10/15/2014.

so i used proc report code like this.

PROC REPORT DATA=REPORT  NOWD LIST

STYLE(HEADER)={BACKGROUND=STGB FOREGROUND=WHITE tagattr='format:m/d/yyyy'};

COLUMN SUMMARY FEB15 JAN15 DEC14 NOV14 OCT14 SEP14;

RUN;

Can anyone know how to get the required format.

in dataset the variable are having (monyy5). and in labels are having (mon-yy). format.

in excel file i want like mm/dd/yyyy format.

Any help would be greatly appreciated.

Esteemed Advisor
Posts: 5,968

Re: how to apply the excel format for column headers

When Excel encounters data that looks like a date, it automatically converts the data to the Excel date value (days from 1899-12-31 with an error on 1900-02-29) and displays the value in the appropriate format for your Windows locale in the input field whenever the cell is selected. Normal behaviour. If you don't want that, try to structure the data so that Excel does not recognize it as a date value. Tinker around a bit with your variable/column names in the report.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 6,704

Re: how to apply the excel format for column headers

Excel doesn't have an inbuilt format for Mon-Year.  Use the tagattr for text data so they come out as you output.  Also, how is the dataset defined?  In your example you show the column names as MMMYY (which as mentioned I think is a bad idea), but no indication of label.  What does:

proc report...;

     define jan15 / "Jan-15" style(column)=[tagattr="..."]; forget what the tag for text is.

SAS Super FREQ
Posts: 8,645

Re: how to apply the excel format for column headers

Hi:

  If the OP is using ODS HTML, as explained above, then TAGATTR won't work. Instead I recommend 2 things:

1) switch from ODS HTML to ODS MSOFFICE2K because Microsoft did not like HTML 4.0 spec for style. ODS MSOFFICE2K makes a "Microsoft-friendly" HTML file.

2) use the HTMLSTYLE= attribute in a style override to set the Date format to a Microsoft date format, as described in this paper: https://support.sas.com/resources/papers/proceedings11/266-2011.pdf on page 14 for HTMLSTYLE. The only difference is that for Header cells the syntax would be style(header)={HTMLSTYLE="mso-number-format:??????"} .

cynthia

Post a Question
Discussion Stats
  • 3 replies
  • 501 views
  • 0 likes
  • 4 in conversation