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: 29
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,868

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

Posted in reply to VikrantSawatkar

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: 11,343

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

Posted in reply to VikrantSawatkar

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: 29

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,868

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

Posted in reply to VikrantSawatkar

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
  • 342 views
  • 2 likes
  • 3 in conversation