The SAS Output Delivery System and reporting techniques

PROC REPORT ODS EXCELXP

Accepted Solution Solved
Reply
Regular Contributor
Posts: 229
Accepted Solution

PROC REPORT ODS EXCELXP

Hi....I have a dataset that I would like to export to an excel file using the Proc Report and ODS EXCELXP. I would like to create seaparate worksheets for each Region and within each Region worksheet, to have separate tables by each ID. Any suggestions....Thanks.

 

Region ID
North P001
  P012
  P015
South P112
  P165
  P167
  P178
East P201
  P222
West P342
  P376
  P380
  P391
Central P412
  P137

 


Accepted Solutions
Solution
‎04-26-2016 08:18 PM
SAS Super FREQ
Posts: 8,868

Re: PROC REPORT ODS EXCELXP

Hi:

  If all you want is a separate table for each ID on each sheet, then that may be possible without using a SAS Macro program.

 

  For example, consider this program that uses a subset of SASHELP.SHOES -- I have a sheet for each region and then a table for each product (the equivalent of your ID) on the sheet for each region:

 

by_region_proc_report.png

 

  Here's the code that produced the above screen shot:

 

proc sort data=sashelp.shoes out=newshoes;
by region product;
where region in ('United States', 'Canada') and
      product contains 'Casual';
run;
   
options nobyline;
ods tagsets.excelxp file='c:\temp\byregion.xml' style=htmlblue
    options(sheet_interval='bygroup' sheet_name='#byval1');
   
	proc report data=newshoes nowd;
	by region;
	column product subsidiary sales inventory returns;
	define product / group;
	define subsidiary / group;
	define sales / sum;
	define inventory / sum;
	define returns / sum;
	break after product / summarize page;
	run;
ods tagsets.excelxp close;

 

cynthia

View solution in original post


All Replies
Super User
Posts: 19,855

Re: PROC REPORT ODS EXCELXP

Since you're changing the page interval - by region switches tabs, by ID creates new tables you'll need a macro unfortunately. 

Make sure to put the ODS tagsets outside of the macro loop.

 

1. Create proc report that works for one Region, using a Where clause to filter the report.

2. Change code from #1 to generate a table per ID

3. Output to Tagsets for single

4. Create a macro loop to wrap code in. 

 

For the macro loop the following is the idea. 

 

%macro loop_reports;
%*create a list of macro variables with each region; proc sql noprint; select distinct region :region1- from mydata; quit; %let nobs=&sqlobs.; *tagset code goes here; %do i=1 to &nobs;
options tagsets.excelxp(sheet_inteval='none');
proc report code goes here;
BY ID; Where region = "&&&region&i"; ****rest of code; run; %end; %*tagset close goes here; %mend loop_reports;

 

Super User
Posts: 10,041

Re: PROC REPORT ODS EXCELXP

proc sort data=sashelp.class out=class;
by sex;
run;
options nobyline;
ods tagsets.excelxp file='/folders/myfolders/xx.xml' options(sheet_name='#byval1');
proc report data=class nowd;
by sex;
run;
ods tagsets.excelxp close;


Solution
‎04-26-2016 08:18 PM
SAS Super FREQ
Posts: 8,868

Re: PROC REPORT ODS EXCELXP

Hi:

  If all you want is a separate table for each ID on each sheet, then that may be possible without using a SAS Macro program.

 

  For example, consider this program that uses a subset of SASHELP.SHOES -- I have a sheet for each region and then a table for each product (the equivalent of your ID) on the sheet for each region:

 

by_region_proc_report.png

 

  Here's the code that produced the above screen shot:

 

proc sort data=sashelp.shoes out=newshoes;
by region product;
where region in ('United States', 'Canada') and
      product contains 'Casual';
run;
   
options nobyline;
ods tagsets.excelxp file='c:\temp\byregion.xml' style=htmlblue
    options(sheet_interval='bygroup' sheet_name='#byval1');
   
	proc report data=newshoes nowd;
	by region;
	column product subsidiary sales inventory returns;
	define product / group;
	define subsidiary / group;
	define sales / sum;
	define inventory / sum;
	define returns / sum;
	break after product / summarize page;
	run;
ods tagsets.excelxp close;

 

cynthia

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 412 views
  • 4 likes
  • 4 in conversation