BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
1 REPLY 1
Cynthia_sas
SAS Super FREQ
Hi:
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.

cynthia
[pre]

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

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

proc print data=shoes;
by region;
var region product subsidiary sales;
run;
ods tagsets.excelxp close;
[/pre]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 888 views
  • 0 likes
  • 2 in conversation