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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.