ODS Excel - Tab output based on data

[SASaholic629]

Is it possible to output to different tabs in Excel based on the value in the data? I have 3 different groupings that I'd like to breakout going down, across, and by sheet name. Can I take the data below to create 2 different tabs called, 'S1' and S2'? Then have 'A1' and 'A2' going across the top of each tab with 'D1' and 'D2' going down each tab?

(I would like to do all of this in one step as I will have about 20 different tabs)

DATA Have;
INPUT Sheet_Grp $ Across_Grp $ Down_Grp $ Value;
DATALINES;
S1	A1	D1	20
S1	A1	D2	25
S1	A2	D1	30
S1	A2	D2	35
S2	A1	D1	40
S2	A1	D2	45
S2	A2	D1	50
S2	A2	D2	55
;RUN;

[ballardw]

---

@SASaholic629 wrote:

Is it possible to output to different tabs in Excel based on the value in the data? I have 3 different groupings that I'd like to breakout going down, across, and by sheet name. Can I take the data below to create 2 different tabs called, 'S1' and S2'? Then have 'A1' and 'A2' going across the top of each tab with 'D1' and 'D2' going down each tab?

(I would like to do all of this in one step as I will have about 20 different tabs)

---

You need to describe what you mean by "one step".

Also how do expect to get the results into Excel? Be default if you use ODS EXCEL output each table created is sent to a different sheet in the work book.

Some procedures can create output "pages" that would be separate tables and hence appear on separate sheets. If you have SAS/Access to PC Files this does basically what you are requesting. This will create a separate table for each level of sheet_grp.

Caveat with working with proc tabulate: if any of the variables that appear on a class statement have missing values the default behavior is to exclude the RECORD from the output.

ods excel file='<path>\example.xlsx';
proc tabulate data=have;
   class sheet_grp Across_Grp  Down_Grp;
   var value;
   table  sheet_grp,
          Down_Grp='',
          Across_Grp=''*value=''*max=''
          ;
run;

ods excel close;

Tne number of lines in a TABLE statement that end in , determine the positions of the variables. In this case the first is the  PAGE expression using the sheet_grp variable, next is the ROW expression using your Down_grp and then the Column expression using the Across_grp variable. Tabulate requires some sort of statistic, default for numeric values appearing on a VAR statement such as value would be SUM. Tabulate also requires specifying if a variable will be Class, used for creating groups, or VAR used for statistics such as sum, mean, max, min, std, and such. Any variable not on one of those two types of statements will generate an error if it appears in the TABLE statement.

If you can process the data with BY group processing you can tell ODS Excel to create a sheet for each level of the by group.

[SASaholic629]

That is exactly what I meant by one step, haha. Didn't want to have to create 20 different proc reports for each tab or whatever. Wasn't familiar with proc tabulate's ability to do that. So thank you, this is a HUGE help.

Couple of follow up questions:

1. How do I get the sheet names to be the name of the data in Sheet_Grp instead of 'Tabulate 1 - Table 1' etc.

2. How do I format the numbers? I tried adding FORMAT value COMMA9.; at the end and that didn't do anything.

[SuryaKiran]

1) sheet_interval='bygroup' sheet_name='#byval1' 

2) use tagattr= to control the formats when writing to Excel.

DATA Have;
INPUT Sheet_Grp :$ Across_Grp :$ Down_Grp :$ Value;
DATALINES;
S1 A1 D1 20
S1 A1 D2 25
S1 A2 D1 30
S1 A2 D2 35
S2 A1 D1 40
S2 A1 D2 45
S2 A2 D1 50
S2 A2 D2 55
;RUN;

proc sort data=have;
by Sheet_grp Down_Grp;
run;

proc transpose data=have out=have_t(drop=_name_) ;
by Sheet_grp Down_Grp;
id Across_Grp;
var Value;
run;

ods _all_ close;
ods excel file="/usr/apps/sasdata/CAO/CAOSec/local_access/CAO_DropBox/Kiran/ODS_Excel/sas_test.xlsx" 
	options(embedded_titles="yes" 
			sheet_interval="bygroup"
			sheet_name='#byval1'
			) ;


proc print data=have_t;
by Sheet_Grp;
var a1 a2/  style(data)={tagattr="format:#,####.00"};;
run;
ods excel close;