Hi:
One other thing you could try is this:
In EG
Tools--> Options --> Results General
Make sure that ONLY HTML is checked under Results Formats
Then under Tasks --> Custom Code
check insert custom code before task and click EDIT
Then type this into the custom Code window:[pre]
ods _all_ close;
ods msoffice2k file='c:\temp\Shop1.xls' style=egdefault
newfile=bygroup;
[/pre]
Click SAVE in the code window.
Then click EDIT for the After code choice and type this:
[pre] ods _all_ close; [/pre]
Then click SAVE to close this custom code window.
Click OK to close the Options window.
Then choose your Data for the shops and choose
Describe --> List Data
Choose your variables for the List data task (or some other task).Be sure to use
your SHOP variable as the "group by" variable. (so that SAS generates a BY
statement for BY SHOP.
You are not REALLY creating an Excel file, but you ARE creating an HTML file
using MSOFFICE2K (Microsoft specific HTML) that Excel can open. EG will only
show you the first file that you create in the project window (Shop1.xls), but, if you
go to the c:\temp directory (outside of EG), then you should see all your SHOP
files there -- SHOP1.xls thru SHOP??.xls that you can then open with Excel.
Now, this does not get them into one workbook, but into multiple workbooks using
the NEWFILE= logic. And, of course, after you're done, you have to remember to
go back and clear out the custom code nodes.
At this point, you'd probably be just as well off to build a code node to either use
PROC EXPORT or the LIBNAME Excel engine to create a single workbook with
multiple sheets in a code node. Here's the Proc Export method:[pre]
proc export data=sashelp.shoes(where=(Region EQ 'Pacific'))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;
proc export data=sashelp.shoes(where=(Region EQ 'Asia'))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Asia";
run;
[/pre]
And here's the LIBNAME method:
[pre]
libname WrkBk excel 'lib_method.xls' ver=2002
data WrkBk.Asia;
set sashelp.shoes;
where region = 'Asia';
run;
data WrkBk.Pacific;
set sashelp.shoes;
where region = 'Pacific';
run;
libname WrkBk clear;
[/pre]
You will need SAS/Access for PC file formats for either of these methods to work.
Or, if you're building a code node anyway and you want to create a workbook for
each shop, you could just put this ODS code into an EG code node:
[pre]
** close the ODS destination that EG opens by default;
ods _all_ close;
** create an MS Office HTML file -- and "fool" the Windows registry by;
** giving the file an XLS extension. When you double click on the file;
** FROM WINDOWS EXPLORER (not EG), you will launch Excel;
ods msoffice2k file='c:\temp\shop1.xls' style=egdefault
newfile=bygroup;
*** your procedure of choice with;
by shop;
run;
ods _all_ close;[/pre]
Good luck,
cynthia