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 |
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:
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
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 = "&&®ion&i";
****rest of code;
run;
%end;
%*tagset close goes here;
%mend loop_reports;
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;
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:
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.