Hi Cynthia_sas,
I want to print reports by each GROUP in the same excel sheet_name. So, Instead of writing multiple PROC PRINTs, I am using macro loops to control proc print.
But the result is, each by group is created in different excel sheet names like temp,temp1,temp2 ...etc.
Below code I am using.
Please suggest.
%macro xls;
title; footnote;
os tagsets.excelXP file="/path/report.xls";
%do i=12 %to 14;
ods tagsets.excelXP options(sheet_intervals='none' sheet_name="temp");
title "&i.";
proc print data=sashelp.class noobs;
where age=&i.;
run;
%end;
ods _all_ close;
%mend;
%xls;
Thanks.
Hi Cynthia_sas,
Thanks for your response though. I do agree with you.
But here my whole idea is each individual value in the By group should have a unique table within the same sheet (sheet_name='sheet 1') one below one, not in a different sheet. If the input dataset changed, then the sheet_name also should change. So, here two things need to handle.
The first one: within the one sheet each value(sashelp.class Age=11,12,14...etc.) should have individual table.
Second: when the input dataset changed, then the proc print is on sheet 2 within the same file.
Example: In sashelp.class Age=11 group table should populate first. On the same sheet below Age=12 group table with title.
So, I am subsetting age variable in sashelp.class through macro loops, instead of multiple PROCs. I tried with ODS HTML with macro loops and its working. But sheet_name is not coming.
Below code I am using:
%macro xls(sheet=);
title; footnote;
ods listing close;
os tagsets.excelXP file="/path/report.xls" new_file='none';
%do i=11 %to 14;
ods tagsets.excelXP options(sheet_intervals='none' sheet_name="&sheet.");
title "&i.";
proc print data=sashelp.class noobs;
where age=&i.;
run;
%end;
ods _all_ close;
%mend;
%xls(sheet=sheet1 );
Please suggest.
Thank you in advance.
Hi:
I rarely look in this forum, since I mainly teach the ODS and Base Reporting classes, along with the stored process classes. However, you posted a similar question in a different forum and I posted a response over there with some sample code. Please look at the use of SHEET_NAME='NONE' that I posted here:
https://communities.sas.com/t5/ODS-and-Base-Reporting/Display-All-BY-groups-in-same-ODS-EXCEL/m-p/32... which shows how to do what I think you want to do.
The BY group is age based on SASHELP.CLASS. If you want the title to reflect the name of the BY group, you need to use #BYVAL in the TITLE statement. However, the titles will not show in the sheet unless you use the embedded_titles='yes' suboption. I am still not convinced you need a macro solution, since it is possible to do everything you seem to want with suboptions and BY group processing.
cynthia
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!