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
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.