Proc Template

[deleted_user]

Does anyone know any good documentation on using proc template to customize excel output. I have sample codes but nothing explaining what the code means

[Cynthia_sas]

Hi:
When you are working with "excel" there are several different methods you could be using:
1) ODS CSV (create a comma separated file)
2) ODS HTML (and other HTML destinations) to create an HTML file that Excel can open
3) ODS TAGSETS.EXCELXP (a Spreadsheet Markup Language XML file) that conforms to the Microsoft specification for Workbook/Worksheet XML.

In ODS CSV, there would only be a TAGSET template involved in the creation of output. For the other 2 methods you could either be talking about needing STYLE template changes or TAGSET template changes. In my experience when talking about Excel, about 90-98% of what people need to do with templates usually involves changing a style template.

There is also a slight chance, that, given your procedure of choice that you might be changing a TABLE template.

When you look at your sample codes, do they start:
[pre]
proc template;
define style ....;
or
proc template
define tagset ...;
or
proc template;
define table...;
[/pre]

Without knowing what type of template you are dealing with, it is hard to help you. However, this paper is from last year's SAS Global Forum --
http://support.sas.com/resources/papers/proceedings09/227-2009.pdf

it is an overview of all 4 template types with some introductory basics on how templates operate. Perhaps that will put things in enough perspective that you can ask more detailed questions or possibly post some of the code.

Or, alternately, you might explain a bit more about what you want to do, such as
"I am creating a summary report of sales and I need to highlight the background color of the sales column to be yellow if the sales are under $500 and green if the sales are over $1000." or "I want to make a workbook with a worksheet for every BY group in my PROC PRINT." or ....perhaps elaborate a bit more on why you think a template change is required.

cynthia

[deleted_user]

Thanks for the response. I am trying to produce an excel spreadsheet(s) that I do not have to go in and format after running the code. For example margins, rows to repeat, percentage. I am trying to use tagsets.excelxp because the options to do these things look much easier, however my output in excel is all xml code. I looked at my tagset listing and excelxp is listed.

[sbb]

You really would benefit by sharing your SAS code.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Here is the proc template I used with tagsets.msoffice2k and it seemed to work ok.

ods path template.tmplmst(update) sashelp.tmplmst(read);

proc sort data= view1.ae out= ae;
by investigatornumber patientnumber;
where relate^= 'D';
run;

proc template;
define tagset template.test;
parent=tagsets.msoffice2k;
define event doc;
start:
put '' NL;
finish:
put "" NL;
end;
define event doc_head;
start:
put "" NL;
put VALUE NL;
finish:
put "" NL;
put "" NL;
end;
end;
run;

ods markup file="v:\temp.xls" tagset=template.test stylesheet='v:\temp.css'
headtext="" ;

proc report data= ae nowd;
column ('this is a test' event);
define event / display 'event' width=128;
run;


However when I try to do a simple tagsets.excelxp such as:

ods tagsets.excelxp file="v:\spacing.xls";

proc report data= ae nowd;
column ('this is a test' event);
define event / display 'event' width=128;
run;


ods tagsets.excelxp close;

It produces output with no errors but it just looks like xml code overlayed in Excel.

[Cynthia_sas]

Hi:
TAGSETS.MSOFFICE2K is an HTML-based destination. It is HTML that conforms to the Microsoft Office 2000 specification, and, as the name implies, is meant to be opened with Office (Word, Excel) 2000 or later.

TAGSETS.EXCELXP is an XML-based destination. It is XML that conforms to the Microsoft 2002/2003 Spreadsheet Markup Language specification. This type of file is meant to be opened with Excel 2002/2003 or later. You cannot open TAGSETS.EXCELXP output using Office 2000. (Nor, can you use the SAS Add-in for Microsoft Office to try to "receive" TAGSETS.EXCELXP output from a SAS Stored Process.)

If you are using Office 2000, you will be unable to open TAGSETS.EXCELXP XML in Excel and have it render correctly. Even if you name the file with a ".XLS" file extension -- you are only fooling the Windows registry into launching Excel in order to open the XML file. That doesn't mean Excel will be able to open and render the XML. (Even if you name the MSOFFICE2K output with .XLS as the extension, you are still creating an HTML file that Excel knows how to open and render. If you looked at the "v:\temp.xls" file using Notepad, you should see HTML tags in the file. Just as if you looked at the "v:\spacing.xls" using Notepad, you should see XML tags in the file.)

With TAGSETS.MSOFFICE2K, you had to change the tagset template in order to change the orientation -- but you still only got one worksheet per HTML file that you created. The beauty of TAGSETS.EXCELXP (if you have the right version of Excel to open the file), is that you can create multi-sheet workbooks automatically and options such as landscape are easy to set without needing a tagset template change.

If you -do- have Office 2002/2003 and the TAGSETS.EXCELXP output will not open in your Excel, then you have a question for Tech Support.

Also, LISTING-only options for PROC REPORT -- like WIDTH=, SPACING=, HEADLINE, HEADSKIP, etc are all ignored (or simply not used) by ODS destinations like HTML, PDF, RTF and TAGSETS.EXCELXP.

cynthia

[deleted_user]

Thanks for the post. I have 2003 on my desktop and only 2000 on the server I'm on. That was the problem. I thought they were both the same.

Now my problem is after much playing around with certain options, I can't get a column to wrap any text. I have used the default_column_width, absolute_column_width, I even turned on the auto_fit_height to 'yes'.

I've also tried a width= in proc report with a flow option on the variable but to no avail.


proc sort data= view1.ae out= ae;
by investigatornumber patientnumber;
where relate^= 'D';
run;



options leftmargin=.25in
rightmargin=.25in
topmargin=.5in
bottommargin=.5in;




ods tagsets.excelxp file="v:\spacing.xls"

style= journal

options ( sheet_name= 'test'
orientation= 'landscape'
row_repeat= '1-3'
scale= '75'
CENTER_HORIZONTAL= 'yes'
gridlines= 'yes'
blackandwhite='yes'
absolute_column_width='20,10'
embedded_titles= 'yes'
auto_fit_height= 'yes');


proc report data= ae nowd style(header)={just=center background=white bordercolor=black borderwidth=2 font_face='Ariel' font_weight=bold font_size=10pt};


column (event intense);
define event / display 'event' width=20 flow style(column)= {background=white bordercolor=black borderwidth=2};
define intense / display 'intensity' width=10 style(column)={background=white bordercolor=black borderwidth=2 };
title1 'this is a test' ;
run;
ods tagsets.excelxp close;