TABULATE format problem - EXCEL XP tagset

[deleted_user]

Hi all,

I am using proc tabulate to report percentages for categorical
variables. I then use the Excel XP tagset to output these tables in
Excel.

I am working on an automated charting process in VBA and facing the
problem below :

In the Excel output, the variable name is printed just above the 1st
modalitie which makes the 1st percentage being a merged Excel cell.

Is there a way to avoid this and print only the variable modalities
rather than (the var name + the var modalities below).

Thanks in advance for your help,

Erwan

[David_SAS]

You can remove row class variable names, e.g.:

[pre]
ods tagsets.excelxp file="tab.xls";
proc tabulate data=sashelp.class;
class sex age;
table sex="", age/row=float;
run;
ods tagsets.excelxp close;
[/pre]
-- David Kelley, SAS

[deleted_user]

Thanks David.

The solution you provided works fine when there is one variable to be reported but when there are 2+ vars it won't work...because one can't make the distinction between one variable and another if the name is blanked.

The thing is that I must have both variables stats to be reported in the same table.
Is there a way to keep the var name but at the same time make the first var modality not being a merged cell when routed via ODS to Excel.

Hope this is clear

Thanks for for your help,

data
test;
input division Q01aclass Q01bclass;
datalines
;
1 1 1
1 2 1
1 3 1
1 1 2
2 3 1
2 1 3
1 2 2
3 2 1
3 1 1
3 3 3
;
run
;
Ods tagsets.ExcelXp file="C:\test.xls";
proc
tabulate data=test (keep=division Q01aclass Q01bclass);
class division Q01aclass Q01bclass ;
table N='Base' colpctn='Column Percent Total' N*(Q01aclass Q01bclass) (colpctn='Column Percent')*(Q01aclass Q01bclass)
, all=
'Total' division ;
run
;
Ods
tagsets.ExcelXp close;

[David_SAS]

We get that request often. We could consider adding an option to the ExcelXP tagset that would turn a single row of row-spanning data cells into two rows. The first row would have all empty data cells, and the second row would have the PROC-generated values.

I will run this suggestion past the ExcelXP tagset developer.

-- David Kelley, SAS

[Cynthia_sas]

Hi:
One possible workaround is to make a category variable that contains the value and the variable name, that forces the variable name (Q01a or Q01b) in each cell -- which still may not be optimal, but will allow you to get rid of the "double wide" cell.

cynthia

[pre]

data test;
length Q01a Q01b $10;
input division Q01aclass Q01bclass;
Q01a = 'Q01a: '||put(q01aclass,1.0);
Q01b = 'Q01b: '||put(q01bclass,1.0);
output test;
return;
datalines;
1 1 1
1 2 1
1 3 1
1 1 2
2 3 1
2 1 3
1 2 2
3 2 1
3 1 1
3 3 3
;
run;

Ods tagsets.ExcelXp file="C:\temp\test.xls";

proc tabulate data=test ;
class division Q01a Q01b ;
table N='Base' colpctn='Column Percent Total' N*(Q01a=' ' Q01b=' ')
(colpctn='Column Percent')*(Q01a=' ' Q01b=' '),
all='Total' division / row=float;
run;
Ods tagsets.ExcelXp close;
[/pre]

[deleted_user]

Thank you Cynthia!!