Desktop productivity for business analysts and programmers

splitting output by variable

Posts: 0

splitting output by variable

I have a query that produces output for sales data. Each region has multiple sales people. I want to have one job that queries a specific region, but creates individual Excel output files by sales person. So for instance, the query for the SouthEast region has 5 sales people. It would generate 5 separate Excel sheets (one for each person).

Can I do this in the "GUI" part of EG, or does it require code?

thanks a lot!
Posts: 8,720

Re: splitting output by variable

In my experience, this would require code. You would have to use the ExcelXP tagset and your could would look something like this. I show every region in a subset of data, instead of every sales person, but this should give you the general idea.

After the ExcelXP file is created in EG, you will have to go to Windows Explorer to open the file. EG 4.1 thinks that any XML file is a SASReport XML file and it doesn't want to use Excel to open the output. Once you get to Windows Explorer or to Excel to open the file, you'll be fine.

Oh, there's just one other little thing. You are not creating a "true" binary Excel file. What you're creating is Microsoft format Spreadsheet Markup Language XML. So Excel 2003 opens the file without complaint. However, Excel 2007 peeks inside the file (if you've given the extension of .xls) and says, 'wait a minute, this is an XML file' -- and yes, even though it's MICROSOFT XML, you get a popup window that says
The file you are trying to open < filename.ext > is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

You just need to click Yes, if you're running Office 2007. I don't know why they do this -- it's their own format, but oh well, they do. If you name the file with a .XML extension, you do NOT get this message from Excel.


proc sort out=shoes;
by region product;
where region in ('Asia', 'Canada', 'Pacific');

ods tagsets.excelxp file='ALL_Region.xls' style=sasweb;

proc print data=shoes;
by region;
var region product subsidiary sales;
ods tagsets.excelxp close;
Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation