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.
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;
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.