BookmarkSubscribeRSS Feed
Ravikumarkummari
Quartz | Level 8

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.

3 REPLIES 3
Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Super FREQ

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 1472 views
  • 0 likes
  • 4 in conversation