BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
VikrantSawatkar
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
Diamond | Level 26

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

3 REPLIES 3
ballardw
Super User

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.

VikrantSawatkar
Obsidian | Level 7

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.

Cynthia_sas
Diamond | Level 26

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 6220 views
  • 3 likes
  • 3 in conversation