topic Re: PROC TABULATE with ODS TAGSET in ODS and Base Reporting

PROC TABULATE with ODS TAGSET

KRUDEL — Mon, 30 Jul 2012 08:23:08 GMT

hi All,

Is there any syntax to export with PROC TABULATE for multiple spreedsheet for excel output? because i tried with this script :

ods Tagsets.ExcelXP file='C:\Training\'

file= 'test.xml'

style = Styles.test2;

proc tabulate missing data=sashelp.class;

class sex age;

table sex=''*(age='' all),n;

run;

ods Tagsets.ExcelXP close;

there is an error when opening the file. Need advice

Thanks

Re: PROC TABULATE with ODS TAGSET

Andre — Mon, 30 Jul 2012 11:51:53 GMT

Krudel

Your code (without style as this could be another problem) is working well under sas 9.3.0

now it is true that the opening of the xml file in the resultsviewer encounter an advertisement

that ask you to allow this opening.

but if you go in the results windows and you double click after on the table1 excel icon

you can see that the xml file is correct and well readed by excel

Don't forget to save this file in a real .xls file or .xlsx/xlsb file

depending of your version of Excel.

André

see perhaps too

http://www.sascommunity.org/wiki/Tips:Automatically_opening_Excel_workbooks_created_by_the_ExcelXP_tags

http://support.sas.com/kb/38/266.html

Re: PROC TABULATE with ODS TAGSET

Cynthia_sas — Mon, 30 Jul 2012 14:29:41 GMT

Hi:

Just to point out that your PROC TABULATE has to create more than 1 table in order to seemultiple sheets in the XML file. But, with an * between SEX and AGE, you are asking for the values of AGE to be shown within values of SEX in the ROW dimension, so, you are only creating 1 table which will appear on 1 sheet.

In addition to Andre's suggestions, I wanted to make sure that you didn't actually have 2 FILE= options:

ods Tagsets.ExcelXP file='C:\Training\'

file= 'test.xml'

style = Styles.test2;

I figured that the first FILE= was probably a typo and you really have PATH=:

ods Tagsets.ExcelXP path='C:\Training\'

file= 'test.xml'

style = sasweb;

ods Tagsets.ExcelXP FILE='C:\Training\test.xml'

style = sasweb;

Another debugging technique would be to use one of the standard styles -- looks like STYLE=styles.test2 is a custom style -- if the file opens when you use SASWEB or HTMLBLUE style, but doesn't open when you use your custom style, that means there could be something wrong with your style specification.

There is also a note about how using TABULATE with a PAGE dimension causes issues, but I don't see that you are using a PAGE dimension in your code.

http://support.sas.com/kb/45/306.html

Last, might be to check your version of TAGSETS.EXCELXP and possibly update it, per these instructions:

http://support.sas.com/kb/32/394.html

If none of that works, then I'd recommend opening a track with Tech Support.

cynthia

Re: PROC TABULATE with ODS TAGSET

KRUDEL — Tue, 31 Jul 2012 08:46:07 GMT

Guys, this is my style proc template :

proc template;

define style styles.test2;

parent=styles.journal;

style body from body / background=transparent;

style header from header / font_weight=bold font_size=10pt

font_style=italic borderrightwidth=1

bordertopwidth=1 borderbottomwidth=2;

style data from data / bordertopwidth=0 borderbottomwidth=0

borderleftwidth=1 borderrightwidth=1;

replace Table from Output /

frame = hsides;

end;

run;

i run on SAS 9.1.3. I tried this example on http://support.sas.com/kb/45/306.html and its doesn't work. Is there any problem if i use different version of SAS?. The point is i want to build excel report based on PROC TABULATE output which it creating multiple spreadsheet. Is there possible for SAS 9.1.3?. Need advice again.

Thanks

Re: PROC TABULATE with ODS TAGSET

Cynthia_sas — Tue, 31 Jul 2012 15:03:34 GMT

Hi:

I no longer have SAS 9.1.3 to test with. Tech Support could test your style template in 9.1.3 with TAGSETS.EXCELXP. One note, if you use the REPLACE statement in a style template, you have to respecify all the style attributes you want to keep:

From the 9.1.3 doc:

"If you use the REPLACE statement to create a style element in the new style

definition, all style elements that inherit from that element inherit the

definition that is in the new style definition. If you want to keep any

attributes that are specified in the definition that is in the parent, you must

respecify them in the definition that you create in the child style definition."

Right now, the way you have created the template, your style element for TABLE ony has 1 attribute and the other needed attributes are going to be undefined.

Also, I'm not sure that BACKGROUND=TRANSPARENT was possible in SAS 9.1.3. In addition the BORDERRIGHTWIDTH, BORDERLEFTWIDTH (etc) style attributes first were production in SAS 9.2 -- but again, Tech Support would know for sure and could help you come up with a working style template.

cynthia

BTW, in SAS 9.3, the code and example in the Tech Support note 45306 worked (without any changes) to create a new sheet for every unique value of AGE.

In SAS 9.2, this code worked (without a custom style template) to create multisheet output:

ods tagsets.excelxp file="c:\temp\multsheet.xml" style=styles.sasweb;

proc tabulate data=sashelp.class;

class age sex;

var height;

table age, sex, height;

run;

ods tagsets.excelxp close;