The SAS Output Delivery System and reporting techniques

Creating Different Worksheet for Each Group By Variables in SAS using ODS Excel

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Creating Different Worksheet for Each Group By Variables in SAS using ODS Excel

Hi All,

 

I am trying to export the data into Excel (97-2003) format. I want to export the data in the excel in such a way that, it will create a separate worksheet for each distinct value of the group by variable.

 

E.g. - I have a dataset which contains Sales Information of Product from different Cities. Now I want to export the data for each distinct city values into a separate worksheet. Means, if it contains sales information from London City, then it will create a separate worksheet in excel for it and it should be named as 'London'. Similarly, if there is some other city name then it should create a different worksheet for it and that city name should be given to it. The number cities may be different and not always be the same.

 

I tried to group the datasets by using 'By variable-name' in proc report while exporting but it is not creating different worksheets.

 

Could you please let me the solution for it.

 

Thank you in advance.


Regards,

Vikrant.


Accepted Solutions
Solution
‎06-06-2017 12:33 PM
SAS Super FREQ
Posts: 8,743

Re: Creating Different Worksheet for Each Group By Variables in SAS using ODS Excel

Hi:

  This code makes 3 sheets for me in 1 workbook -- using PROC REPORT with BY group processing. My dataset has 3 values for AGE, based on the WHERE in the PROC SORT step and I get 3 sheets in my workbook. If you run my code and get 3 sheets in your resulting workbook, then that suggests there is something else wrong with your original code.

 

sheet_3_bygroup.png

 

cynthia

 

proc sort data=sashelp.class out=class;
where age in (12 13 14);
by age;
run;
  
title;
options nobyline;
ods tagsets.excelxp file='c:\temp\showby.xml'
    style=htmlblue options(sheet_interval='bygroup' sheet_name='Age_#byval1'
                           suppress_bylines='yes');
  
proc report data=class;
by age;
column age name sex height weight;
run;
ods tagsets.excelxp close;
options byline;

View solution in original post


All Replies
Super User
Posts: 10,497

Re: Creating Different Worksheet for Each Group By Variables in SAS using ODS Excel

What method were you using to send data to Excel? You would set the option(sheet_interval='bygroup') if using ODS Tagsets.ExcelXP or ODS Excel destinations. Others aren't likely to honor any sheet setting.

Contributor
Posts: 27

Re: Creating Different Worksheet for Each Group By Variables in SAS using ODS Excel

I am using ODS Tagsets.ExcelXP and had set sheet_interval='bygroup' option already. I get all the observations grouped by Cities in one excel worksheet only which is not what I want.

Solution
‎06-06-2017 12:33 PM
SAS Super FREQ
Posts: 8,743

Re: Creating Different Worksheet for Each Group By Variables in SAS using ODS Excel

Hi:

  This code makes 3 sheets for me in 1 workbook -- using PROC REPORT with BY group processing. My dataset has 3 values for AGE, based on the WHERE in the PROC SORT step and I get 3 sheets in my workbook. If you run my code and get 3 sheets in your resulting workbook, then that suggests there is something else wrong with your original code.

 

sheet_3_bygroup.png

 

cynthia

 

proc sort data=sashelp.class out=class;
where age in (12 13 14);
by age;
run;
  
title;
options nobyline;
ods tagsets.excelxp file='c:\temp\showby.xml'
    style=htmlblue options(sheet_interval='bygroup' sheet_name='Age_#byval1'
                           suppress_bylines='yes');
  
proc report data=class;
by age;
column age name sex height weight;
run;
ods tagsets.excelxp close;
options byline;
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 267 views
  • 2 likes
  • 3 in conversation