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

I am facing one problem while using ODS HTML to generate excel.

This is my code

ODS HTML FILE=&fl. HEADTEXT="<STYLE> .test1 {mso-width-source:userset;width:1000pt}

.test2  {MSO-NUMBER-FORMAT:\@}     </STYLE>";

proc print data=import.final label noobs;

title ;

footnote;

var a/ style={htmlclass="test1"};

var b -- z/style={htmlclass="test2"}

;

run;

ods html close;

Now

1) I have to apply character format while reading all the columns from dataset, because I hav values like 0.80, o.90 etc.

2) I have to apply the column width to the column a only, as it has VERY LONG text values.

My question is how can I apply both these properties in ODS HTML. The way I am doing it currently(shown in code above), only one of them works

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ


Hi:

  HTMLCLASS (in my experience) works best with HTML files that follow the use of CSS Class Selectors, as outlined in the HTML 4.0 spec -- with files that are supposed to be opened by a browser. I've always found Excel to be a bit picky about CLASS selectors -- especially since ODS HTML makes HTML 4.0 compliant tags, which, generally, Excel is not very happy about opening. If you look at a Microsoft STYLE section versus HTML 4.0 style section, you will see a LOT of differences in the way the 2 style sections look.

  That's why I use ODS MSOFFICE2K as the destination -- it is "Microsoft-friendly" flavor of HTML -- it is the flavor of HTML that Microsoft designed when they decided that they didn't like the HTML 4.0 specification. Rather than using CLASS selectors, I find that using a simple HTMLSTYLE attribute to pass in my Microsoft specific formats is what always works best. Microsoft has a special (different) way that it uses CLASS selectors in Microsoft version of HTML -- it is not standard CSS -- which is what HTMLCLASS is designed to produce as a style override. And, HTMLSTYLE is designed to put a style override down in the <TR> or <TD> tag, where Microsoft will (generally) respect the override.

  The other thing I note is that generally, PROC PRINT "likes" for you to specify the area in the STYLE override - -such as:

style(header)={...} or style(data)={...} -- that's because some of the formats that you want to specify will or should only be applied to the "data" cells and not the "header" cells in the report.

  I thought 1000 pt was too big, so in my sample program, I used 200pt in order to get all the columns on the same sheet when Excel opened the HTML file. Note that I gave the file a .XLS extension just so I could be lazy and double click from Windows Explorer and launch Excel instead of launching a browser, (if I gave an extension of .HTML). Of course, when you use .XLS as the file extension, you are not creating a true, binary Excel file -- you are just fooling the Windows registry into launching Excel when the file icon is double clicked. You can look at the output file in Notepad to see that it reallly is HTML in the output file created by ODS.

  The screenshot shows all the code I submitted and the output opened in Excel. Note that all my numeric columns were treated as character and that my NAME column is bigger than the default width. The techniques that I show in this program were outlined in my SAS Global Forum paper in 2011: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

cynthia


use_mso_htmlstyle.png

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ


Hi:

  HTMLCLASS (in my experience) works best with HTML files that follow the use of CSS Class Selectors, as outlined in the HTML 4.0 spec -- with files that are supposed to be opened by a browser. I've always found Excel to be a bit picky about CLASS selectors -- especially since ODS HTML makes HTML 4.0 compliant tags, which, generally, Excel is not very happy about opening. If you look at a Microsoft STYLE section versus HTML 4.0 style section, you will see a LOT of differences in the way the 2 style sections look.

  That's why I use ODS MSOFFICE2K as the destination -- it is "Microsoft-friendly" flavor of HTML -- it is the flavor of HTML that Microsoft designed when they decided that they didn't like the HTML 4.0 specification. Rather than using CLASS selectors, I find that using a simple HTMLSTYLE attribute to pass in my Microsoft specific formats is what always works best. Microsoft has a special (different) way that it uses CLASS selectors in Microsoft version of HTML -- it is not standard CSS -- which is what HTMLCLASS is designed to produce as a style override. And, HTMLSTYLE is designed to put a style override down in the <TR> or <TD> tag, where Microsoft will (generally) respect the override.

  The other thing I note is that generally, PROC PRINT "likes" for you to specify the area in the STYLE override - -such as:

style(header)={...} or style(data)={...} -- that's because some of the formats that you want to specify will or should only be applied to the "data" cells and not the "header" cells in the report.

  I thought 1000 pt was too big, so in my sample program, I used 200pt in order to get all the columns on the same sheet when Excel opened the HTML file. Note that I gave the file a .XLS extension just so I could be lazy and double click from Windows Explorer and launch Excel instead of launching a browser, (if I gave an extension of .HTML). Of course, when you use .XLS as the file extension, you are not creating a true, binary Excel file -- you are just fooling the Windows registry into launching Excel when the file icon is double clicked. You can look at the output file in Notepad to see that it reallly is HTML in the output file created by ODS.

  The screenshot shows all the code I submitted and the output opened in Excel. Note that all my numeric columns were treated as character and that my NAME column is bigger than the default width. The techniques that I show in this program were outlined in my SAS Global Forum paper in 2011: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

cynthia


use_mso_htmlstyle.png
maggi2410
Obsidian | Level 7

Thankyou so much Cynthia. Things seem to work now for me. But now the problem is I am getting the background color as Gray. But I want it to be white. How can I change it?

I guess for Proc report the code is:

style(report)={background=white}

But how and where should I put in it proc print, ie in the type of code , you put it in your screen shot.

Thanks in advance.

Andre
Obsidian | Level 7

Maggi

proc print data=...   style(table)={background=white}

is i think the answer for proc print

Andre

maggi2410
Obsidian | Level 7

Hi ANdre. That doesnt work.

I dont know how to get teh background as white. I am using the foloowing code:

ODS msoffice2k FILE=&fl. ;

proc print data=inds label noobs style(report)={background=white};

var a/style(data)={htmlstyle="width:'500pt'"} ;

var b -- c/  style(data)={htmlstyle="mso-number-format:'\@';"};

run;

Can nayone help me on this.

Thanks!

Cynthia_sas
SAS Super FREQ

Maggi:

  Did you copy my code? If you used the MSOFFICE2K destination and style=sasweb, you should get a white background under the table. But, the rest of the cells in the sheet -- the "other" cells not bounded by the table will be gray, that is an artifact of Excel and how it opens HTML markup files. If you want the rest of the background, of the "unused" cells to be white, I think you have to change the style template.

  Or, switch to using TAGSETS.EXCELXP -- by default, the unused cells will be white . The syntax will be slightly different.

cynthia

ods tagsets.excelxp file='c:\temp\xpclass.xml' style=sasweb

    options(absolute_column_width='30,10,10,10,10');

 

proc print data=sashelp.class label noobs;

  var name;

  var age -- weight/style(data)={tagattr="FORMAT:@"};

run;

 

ods _all_ close;

maggi2410
Obsidian | Level 7

Thanks again Cynthia. Its my bad. Actually I was using sasweb with proc print, instead of using it with proc.

Anywaz, now I used style=styles.minimal. And everything worked for me.

Thanks for ur help. 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!

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
  • 6 replies
  • 3091 views
  • 1 like
  • 3 in conversation