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
SAS Super FREQ

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
SAS Super FREQ

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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