topic Creating Different Worksheet for Each Group By Variables in SAS using ODS Excel in ODS and Base Reporting

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

VikrantSawatkar — Mon, 18 Jul 2016 17:08:48 GMT

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.

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

ballardw — Mon, 18 Jul 2016 17:58:15 GMT

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.

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

VikrantSawatkar — Mon, 18 Jul 2016 18:02:20 GMT

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.

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

Cynthia_sas — Mon, 18 Jul 2016 20:07:56 GMT

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.

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;