Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

[VikrantSawatkar]

Hi All,

I want to print a report for 10 users (for each user separate report) and I have let's say 20 datasets, out of which I want to print 5 datasets in one worksheet1, 4 in Worksheet2 and rest of the datasets in worksheet3 in one excel file.

I have written a (partial) code below which takes a sheet_name, dataset_name(which I want to print), Title and columns which need to be print. The code does not give me any error or warning while executing. However, whenever the exporting is done, instead of creating 3 worksheets, it creates 20 worksheets in a single Excel for a one user.

Could you please help me here. Many Thanks in advance. 

proc sql noprint;
select distinct count(Use_name) into:User
from Users;

select distinct Use_name into: User1 - : User%Eval(&sub)
From Users;

select count(*) into : Sheet_No

from (select distinct Sheet_name from ABC);

select distinct Sheet_name into : Sheet_No1 - : Sheet_No%Eval(&Sheet_no)

from ABC;

quit;

%macro Print_Report;

%local i;
%do i = 1 %to &User;
%do;

ods noresults;
ods listing close;
ods tagsets.ExcelXP file="Path\&&User&i. - Report1 - &Date1..xls";

%macro Print_Excel;
%local j;
%do j = 1 %to &Sheet_No.;

data DEF;
set ABC;
where Sheet_number = "&&Sheet_No&j.";
run;

ods tagsets.ExcelXP options (embedded_titles='yes' Orientation='Landscape'sheet_name="&&Sheet_No&j."
fittopage='yes' Pages_FitWidth ='yes'Pages_FitHeight = 'yes' center_horizontal='yes')
style=sansprinter;

%macro Sheet_Print(Heading = , dataset = , Var = );

PROC SQL NOPRINT;
SELECT count (*) INTO : Nbr_Obsa FROM &dataset.
WHERE User eq "&&User&i." ;
QUIT;

%IF &Nbr_Obsa= 0 %THEN
%DO;
DATA NoObs;
Comment = "No Records Found";
RUN;

proc report data = NoObs nowd wrap
style(header) = {background=cx99ccff foreground=black font_size =10pt just = c rules = all frame = box font_weight = bold borderwidth = 1pt verticalalign = m}
style(column) = {font_size = 8pt borderwidth = 1pt rules = all frame = box} split = "$#$";

COLUMN Comment;

TITLE1 height = 12pt bold Italic "&Heading.";
RUN;
%END;

%ELSE
%DO;
proc report data = &dataset. nowd wrap
style(header) = {background=cx99ccff foreground=black rules = all frame = box font_size = 11pt just = c font_weight = bold borderwidth = 1pt verticalalign = m}
style(column) = {font_size = 10pt borderwidth = 1pt rules = all frame = box} split = "$#$";

COLUMN &Var.;

TITLE1 height = 12pt bold Italic "&Heading.";
RUN;
%END;
%MEND Sheet_Print;

%local k;
%do k = 1 %to &table.;

data _null_;
set pp22;
where Sequence = &&table&k.;
call symput('title',trim(Form_Name));
call symput('Column',trim(Col));
call symput('data',trim(Form));
run;

%Sheet_Print(Heading = &title, dataset = &data, Var = &Column);
%end;

%end;
%mend Print_Excel;

%Print_Excel


ods tagsets.ExcelXP close;
ods listing;

options missing = .;
%end;
%mend;

%Print_Report

[Reeza · Accepted solution]

You use sheet_interval to control the sheet destination in TAGSETS.EXCELXP. I don't see that anywhere in your code. 

[Reeza]

I think you're also misunderstanding how sheet_name works. 

It only names the sheet. 

The way tagsets work is in a linear fashion. 

All x procedures are printed to a sheet and then it moves to a new sheet. You cannot go back and forth and control the output to which sheet. You need to design the process such that all that should be one one sheet is there and then switch the sheet_interval option to start a new sheet and so forth. 

See the example entitled 'Manual Multiple Worksheets' which is essentially what you're trying to accomplish. 

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#manual

If you're on SAS 9.4 TS1M3 then you can also try ODS EXCEL which will create a native XLSX file rather than an XML file, which is what TAGSETS.EXCELXP creates. Or if you want more control over exporting mutliple sheets and are not concerned with formatting, see this user written macro that exports to a native XLSX file and you have full control. Full control does also mean more work though, because you have to specify more things. 

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

[VikrantSawatkar]

Hi Reeza,

Thank you for the information and help.

Now I am able to generate the desired report. I had included sheet_interval option in the code.

Regards,

Vikrant Sawatkar