topic Re: Split table in SAS Enterprise Guide

Split table

Stephane — Fri, 06 Apr 2007 15:41:05 GMT

Hi

Suppose I have in EG 4 a table that shows customers by shop : customer_ID, Shop_ID, Interesting_informations ...

How can I create a table by shop to export each of them into a separate excel file ?

In SAS base and a %do loop I see very well, but with EG 4 tasks ... 😕

Ho do you do that ?

TIA

Stephane

Re: Split table

DavidHenderson — Fri, 06 Apr 2007 19:57:33 GMT

I am not sure I understand completely, but you should be able to create multiple filter/query nodes, one for each shop. The result would be a table for each query.

For example, if I have order data that includes the sales region (North South, East, West). I could create a query node in the project that sets the filter (region EQ "North"), another query node with the filter (Region EQ "South"), etc.

I recognize that this isn't going to be simple if you have lots of unique values, but if it's a known set of values, it should be approachable.

Re: Split table

Stephane — Sat, 07 Apr 2007 08:23:51 GMT

Hi,

no I think you understood 🙂

the idea is :

data shop1 shop2;
set shops;
if shop_ID = 1 then output shop1;
else if shop_ID = 2 then output shop2;
run;

Proc export data= shop1; ... run;

I have to many shops to do that manually and I have the same thing to do by region after , and so on.

the final result would be an excel file by shop in order to publish each of them to each shop.
I relatively open to publish the files in another way if the solution exists.
Otherwise, I go on SAS base node to do that.

Re: Split table

Doc_Duke — Mon, 09 Apr 2007 16:54:01 GMT

Stephane,

Actually, that's exactly the process that David proposed; the queries are the functional equivalent of your data steps. It is a bit cumbersome and does not support the automation for an unknow list of shops or regions.

There are several examples of macros on SAS-L of base code to export a series of datasets where the number and names of the datasets are a function of the incoming data. If you have licensed the additional extensibility features, you could incorporate the macro directly into your EGuide flow. Otherwise, you could incorporate them through a code node.

Doc Muhlbaier

Re: Split table

JohnH — Tue, 10 Apr 2007 14:26:55 GMT

I am not sure this will work for you, but if you use the ODS Tagsets.EXCELXP system and use proc tabulate, and do it "by ID", SAS will put each table for each ID on a separate sheet withing the same XLS file. That is not a separate XLS file for each ID, but it may help or work. If you have a large number of IDs, it is a one-liner to create the separate sheets.

Re: Split table

Stephane — Tue, 10 Apr 2007 18:34:40 GMT

thank you for your answer.

In fact, ExcelXp asks to use a recent Excel version, and I don't have this.

About the automation, I agree I can use macro code, what I said at the beginning of this post. But I would want to see with the true EG tasks.

Thank you 🙂

Re: Split table

Cynthia_sas — Tue, 10 Apr 2007 20:02:52 GMT

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