Proc Tabulate

[deleted_user]

I have the basic proc tabulate down. I am now working with OBS and multi-sheet excel workbooks. My question is this...

Once I add the OBS tagsetsets.excelxp, some of my label names in my tables disappear in my out put. Also, the breaks inbetween my out put go away as well.

Does this require further formatting? Or is this something that will manually have to be corrected?

[Cynthia_sas]

Hi:
You mean ODS TAGSETS.EXCELXP??? not OBS???

At any rate, I'm not sure I have ever seen labels disappear when I didn't want them to. What breaks are going away??? Do you have multiple tables created by PROC TABULATE. The default TAGSETS.EXCELXP behavior is to make one worksheet for each table. Or, do you mean the SAS Title statements go away? With TAGSETS.EXCELXP, the titles get put into the worksheet Header area (by default).

This is a useful paper about using ODS TAGSETS.EXCELXP:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

Otherwise, for more help, you might see whether you can duplicate the behavior using sashelp.shoes or sashelp.class data and then post your code here with a description of -exactly- what's happening, like "the label for NAME is disappearing when Excel opens the file" or "the title statement is going away".

cynthia

[deleted_user]

Yes. Multiple tables are being created for each tabulate. I have included my code. I can't seem to find how to keep the tables seperated in each sheet.

[Cynthia_sas]

Hi:
I don't understand what you mean by keeping the "tables separated in each sheet". For example, when I run the code below, which is a simplified version of one of your TABULATE steps, I have PRODTYPE in the page dimension, REGION and COUNTRY crossed with ACTUAL and PREDICT in the row dimension and MONTH in the column dimension.

In SASHELP.PRDSALE, PRODTYPE has only two possible values -- either FURNITURE or OFFICE. So when I run this code I get two sheets in Excel, a sheet/table for FURNITURE and a sheet/table for OFFICE. There is only one table on the first sheet and only one table on the second sheet. Hence, my confusion with what you say you're getting/seeing in Excel.

If you want the page dimension information to be inside the "box" of the table, you can do that by changing the BOX= options, as shown below:
[pre]
/ box={label=_page_ style={background=black}} row=float;
[/pre]

cynthia

[pre]
ods listing close;
ods tagsets.ExcelXP file='c:\temp\twotable.xls' style=sasweb
options(sheet_name='Summary By Campaign');

proc tabulate data=prdsale;
where quarter=1;
class prodtype region country month /
S=[background=black foreground=white];
classlev prodtype month /
S=[background=black foreground=white];
var actual predict ;
table prodtype='Campaign:',
region*country*(actual predict),
month='Month'
/ box={style={background=black}} row=float;
label actual='Act' predict='Pred'
region='Region' country='Country';
keylabel SUM = ' ';
run;
ods tagsets.excelxp close;
[/pre]

PS:
I also notice that you are using OUT= in both TABULATE steps and your OUT= value is the same for both steps (OUT=Balance_Generation_Final):
[pre]

proc tabulate data=bal_gen_calc order=data out=Balance_Generation_Final ;
ods tagsets.excelxp options(sheet_name='Market Segment');
proc tabulate data=bal_gen_calc order=data out=Balance_Generation_Final ;
[/pre]

If you only want Excel output, you do not need to create an output dataset.

[deleted_user]

I forgot to include code:

ods listing close;
ods tagsets.ExcelXP file='c:\PC_SAS\Balance_Generation_Final.xls'options(sheet_name='Summary By Campaign');

proc tabulate data=bal_gen_calc order=data out=Balance_Generation_Final ;
where _type_ in (29);
class campaign_type term rate dte;
classlev Dte/S=[background=black foreground=white];
var tran_amt apr offer_term;
table Campaign_Type={label='Campaign:' s=[background=black foreground=white]} ,
term={label='Term's=[background=black foreground=white]}
*rate={label='Rate's=[background=black foreground=white]}
*(tran_amt*f=dollar15.2 apr*f=percent10.2 offer_term),
Dte={label='Post Month' s=[background=black foreground=white]}
/ rts=25;
label tran_amt='Balances';
run;quit;
ods tagsets.excelxp options(sheet_name='Market Segment');
proc tabulate data=bal_gen_calc order=data out=Balance_Generation_Final ;
where _type_ in (0,31);
class campaign_type mkt_seg term rate dte;
classlev Dte/S=[background=black foreground=white];
var tran_amt apr offer_term;
table Campaign_Type={label='Campaign:' s=[background=black foreground=white]}*MKT_SEG ,
term={label='Term's=[background=black foreground=white]}
*rate={label='Rate's=[background=black foreground=white]}
*(tran_amt*f=dollar15.2 apr*f=percent10.2 offer_term),
Dte={label='Post Month' s=[background=black foreground=white]}
/ rts=25;
label tran_amt='Balances' mkt_seg=' Market Segment:';
run;quit;
ODS tagsets.ExcelXP close;
ods listing;

[deleted_user]

Thanks Cynthia! That helped.

I have one more question: In stead of /box=" Name", Is there any way to get a class variable in the upper left hand box that is blank on the output?

[Cynthia_sas]

Hi:
Your choices for BOX= are:
BOX=_PAGE_ (if your table has a PAGE dimension)
BOX="Text string"
BOX=Variable

as described in the TABLE statement documentation:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473740.htm#a003068097

If your CLASS variable is in the PAGE dimension, then BOX=_PAGE_ will put the PAGE info in the TABLE box. If you want a different CLASS variable name or label, then BOX=variable will put the CLASS variable name (or label, if there is one) in the box.

The advantage of BOX=_PAGE_ is that if you have 2 CLASS variables in the PAGE dimension, then BOTH page dimension variables and their values will be put in the box area. Otherwise, you can only have 1 variable name or label if you use the BOX=variable syntax.

cynthia