how to apply the excel format for column headers

[Ravikumarkummari]

Good day all,

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

summary	feb15	jan15	dec14	nov14	oct14	sep14	aug14
count of account value	203	300	450	500	600	650	700
sum of account value	150000	200000	30000	350000	150000	260000	450000
count of account value	500	400	300	200	150	250	350
sum of account value	10000	14000	15000	25000	30000	45000	50000

i have applied label for columns.

Summary-Details	feb-15	jan-15	dec-14	nov-14	oct-14	sep-14	aug-14
count of account value	203	300	450	500	600	650	700
sum of account value	150000	200000	30000	350000	150000	260000	450000
count of account value	500	400	300	200	150	250	350
sum of account value	10000	14000	15000	25000	30000	45000	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.

[Kurt_Bremser]

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.

[RW9]

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.

[Cynthia_sas]

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