BookmarkSubscribeRSS Feed
jfriedman08
Calcite | Level 5

Sorry if this is the wrong place, this is my first post.

 

Basically I need to make >100 tables using the Output Delivery System.  My proc tabulate statement itself works fine on its own, but the ods is not.

 

%let question=value;
ods html body = '&question.xls'
style=minimal;

 

              here is the proc tabulate that is working fine

 

ods html close;

 

Now, the excel file is successfully being created, but it is literally called &question.xls, when I would want it to be called value.xls so it doesn't continually overwrite itself.

 

If there's a way to throw an array in here and just make one excel file with all >100 tables, that would also be good.  I've been using SAS for  about a month so sorry if these are totally n00b questions and thank you for reading and for any help you can give me.

6 REPLIES 6
rayIII
SAS Employee

Hi. Just to address the first part of your question, change your first ODS statement to:

 

ods html body = "&question..xls" style=minimal;

 

jfriedman08
Calcite | Level 5

You are a gangsta of the highest order... thank you!!!!

rayIII
SAS Employee

Haha.. you're welcome. 

 

Ray

Cynthia_sas
SAS Super FREQ

Hi, to answer your second question, you do NOT need a DO loop, if BY group processing will work for you. The first example makes one sheet for every BY group. I limited by BY groups to 3 just to keep it simple. The second example puts all 3 tables on 1 worksheet and uses the PAGE dimension instead of BY group processing. But, you have to shift from using ODS HTML to using ODS TAGSETS.EXCELXP, which is an XML-based destination that creates Microsoft Spreadsheet Markup Language XML output.

 

cynthia

 

proc sort data=sashelp.shoes out=newshoes;
  where region in ('Asia', 'Canada', 'Pacific');
  by region product;
run;
   
** 1: makes one sheet for every BY group;
ods tagsets.excelxp file='c:\temp\showby.xml' style=htmlblue
    options(sheet_interval='bygroup' sheet_name='#byval(region)' doc='Help');
  
proc tabulate data=newshoes;
  class product;
  var sales inventory;
  by region;
  table product all,
        sales='Sales'*(sum mean) inventory='Inventory'*max;
  run;
ods tagsets.excelxp close;
  
** 2: makes one sheet with ALL the tables on that single sheet;
ods tagsets.excelxp file='c:\temp\showsingle.xml' style=htmlblue
    options(sheet_interval='none' sheet_name='All Regions' doc='Help');

proc tabulate data=newshoes;
  class region product;
  var sales inventory;
  table region, 
        product all,
        sales='Sales'*(sum mean) inventory='Inventory'*max /box=_page_;
  run;
ods tagsets.excelxp close;
smitham
Fluorite | Level 6

Thank you! 

 

Reeza
Super User

And if you have SAS 9.4 you can look into ODS Excel. 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 3895 views
  • 0 likes
  • 5 in conversation