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