ods excel file

[Tecla1]

Good afternoon, 

I'm not able in any way to put more sheet in the same file.

Please help me.

In attached the data.

TNKS

%macro multiple (DATA=,);
    /* Conta quante tabelle ci sono */
     proc sql ;
        select count(*) into :ntot
        from Riepilogo_righe_2;
    quit;

    %do i = 1 %to &ntot.;

        /* Prende il nome della tabella i-esima */
        proc sql ;

		select LEFT(trim(TABELLA)), LEFT(trim(DIREZIONE)) 
		
		into :nome, :des 

            from Riepilogo_righe_2
            where monotonic() = &i ;
        quit;
%put &nome.; 
  %PUT &NTOT.;
 %put &DES.;

proc template;
  define style styles.CenteredCellsExcel;
    parent = styles.normal;
    class header /
   
      vjust = middle;
    class data /

      vjust = middle;
  end;
run;





%if %sysfunc(find(&nome, ADDEBITI, i)) > 0 %then %do;
        %put Condition 1 is met;
        /* Add your SAS statements here */


ods excel file="/sas_share/NAS_SDR_814/Controlli 2_1/2026/Sospesi_Contabili_di_Filiale/SaS/OUTPUT/Totale_campioni_polizze_addebiti_&DATA._&des..XLSX" style=styles.CenteredCellsExcel   ;
ods excel options( absolute_column_width='11,20,10,20,10,20,30,12,25,30,10,15,50,15,18,10,15,25,50,20,15,25,15,20,20,50,15,20,10,10,25,35,30,25,35,25,10,10,15,20,15,10,15,25,25,25' row_heights='30,30' 	
    Center_Vertical= 'yes'
    Center_Horizontal= 'yes' 
    sheet_name='Addebiti'
	sheet_interval='none'
    embedded_titles='no'
	GRIDLINES= 'ON'
	AUTOFIT_HEIGHT = 'ON'
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    
);

proc print data=new.&nome. 
	style(report)=[fontfamily="Arial" fontsize=10pt];
   /*define AGGIORNAMENTI / style(header)=[background=#009597 foreground=white font_weight=bold] ;*/	
run;
/*ods excel close;*/

    %end;

%if %sysfunc(find(&nome, POLIZZE, i)) > 0 %then %do;
        %put Condition 2 is met;
        /* Add different statements here */

ods excel file="/sas_share/NAS_SDR_814/Controlli 2_1/2026/Sospesi_Contabili_di_Filiale/SaS/OUTPUT/Totale_campioni_polizze_addebiti_&DATA._&des..XLSX" style=styles.CenteredCellsExcel   ;
ods excel options( absolute_column_width='11,20,10,20,10,20,30,12,25,30,10,15,50,15,18,10,15,25,50,20,15,25,15,20,20,50,15,20,10,10,25,35,30,25,35,25,10,10,15,20,15,10,15,15,15,20,25,25' row_heights='30,30' 	
    Center_Vertical= 'yes'
    Center_Horizontal= 'yes' 
    sheet_name='Polizze'
	sheet_interval='none'
    embedded_titles='no'
	GRIDLINES= 'ON'
	AUTOFIT_HEIGHT = 'ON'
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    
);


proc print data=new.&nome. 
	style(report)=[fontfamily="Arial" fontsize=10pt];
   /*define AGGIORNAMENTI / style(header)=[background=#009597 foreground=white font_weight=bold] ;*/	
run;
ods _all_ close ;

    %end;
    %else %do;
        %put No conditions were met;
    %end;

	%END;

	%mend multiple;
%multiple (DATA=&DATA.);

[Tom]

Can you clarify what you want to happen exactly?

Do you want different PROC steps to generate different sheets?

filename xlsx "myfile.xlsx";
ods excel file=xlsx;
ods excel options(sheet_name="PROC FREQ");
proc freq data=sashelp.class;
  tables sex;
run;
ods excel options(sheet_name="proc print");
proc print data=sashelp.class;
run;
ods excel close;

Note it will be faster to get a working example of the code you want to generate before trying to use a code generation tool, such as a SAS macro, to generate that code.

[Tecla1]

Tnks for your kindly replay.

If a certain condition is met (finding "ADDEBITI" or "POLIZZE" in a list of table name – `%if %sysfunc(find(&nome, ADDEBITI, i)) > 0 %then %do;`), I need to export the table to the same Excel file but to the "ADDEBITI" or "POLIZZE" sheet.

[Quentin]

Can you please post the log from running the code? 

Also please describe the problem you're having.  Are there errors in the log?  Is the Excel output created but something is wrong about it?

[Tom]

---

@Tecla1 wrote:

Tnks for your kindly replay.

If a certain condition is met (finding "ADDEBITI" or "POLIZZE" in a list of table name – `%if %sysfunc(find(&nome, ADDEBITI, i)) > 0 %then %do;`), I need to export the table to the same Excel file but to the "ADDEBITI" or "POLIZZE" sheet.

---

That does not help much since it is not clear where the list of table names is.

But it seems to me if you want to set the name of the SHEET in the XLSX workbook then you should either put it into a macro variable.

ods excel options(sheet_name="&sheetname");

You could generate different sheet names based on some test you want to do.  So perhaps something like:

%let sheetname=default_name;
%if %sysfunc(findw(&out,addebiti,%str( ),i)) %then %do;
  %let sheetname=ADDEBITI;
%end;
ods excel options(sheet_name="&sheetname");

Make sure to open and close the workbook only once. 

ods excel file="myfile.xslx";

%mymacro(sheetname=ADDEBITI);
%mymacro(sheetname=SomethingElse);

ods excel close;

[Quentin]

This is not how I would recommend structuring the code, there are better ways to solve this problem.

But as I had 15 minutes before leaving for a holiday weekend, and I was enthralled by the Italian, I took your code, simplified it a bit, and made some test data.  

The below code I think does what you would want.  You can see I pulled the ODS Excel statement outside of the loop, and made a few other changes.

You can play with this, or with this working example, someone else will write a better-designed macro solution for you.  This is a common macro problem: read in a control dataset with a list of datasets, and print each dataset.  

*make some test data;

data ADDEBITI ;
  set sashelp.class ;
run ;

data POLIZZE ;
  set sashelp.shoes ;
run ;


*control dataset with list of datasets to print;
data Riepilogo_righe_2 ;
  TABELLA='ADDEBITI' ; DIREZIONE='class' ; output ;
  TABELLA='POLIZZE' ; DIREZIONE='shoes' ; output ;
run ;
  

%macro multiple (DATA=,);
  /* Conta quante tabelle ci sono */
  proc sql noprint;
    select count(*) into :ntot
    from Riepilogo_righe_2;
  quit;

  ods excel file="%sysfunc(pathname(work))/foo.xlsx"   ;

  %do i = 1 %to &ntot.;

    /* Prende il nome della tabella i-esima */
    proc sql noprint;
      select LEFT(trim(TABELLA)), LEFT(trim(DIREZIONE)) 
      into :nome, :des 

      from Riepilogo_righe_2
      where monotonic() = &i ;
    quit;

    %put &nome.; 
    %PUT &NTOT.;
    %put &DES.;

    %if %sysfunc(find(&nome, ADDEBITI, i)) > 0 %then %do;
      %put Condition 1 is met;
      /* Add your SAS statements here */


      ods excel options( 
        sheet_name='Addebiti'
      );

      proc print data=&nome.;
      run;
    %end;

    %else %if %sysfunc(find(&nome, POLIZZE, i)) > 0 %then %do;
        %put Condition 2 is met;
        /* Add different statements here */

      ods excel options(
        sheet_name='Polizze'
      );

      proc print data=&nome. ;
      run;

    %end;

    %else %do;
        %put No conditions were met;
    %end;

  %END;

  ods _all_ close ;	

%mend multiple;

%multiple()

[Tecla1]

Good Morning....
Tnks fo your replay... I receive this error:
ERROR: Insufficient authorization to access /Totale_campioni_polizze_addebiti__&des..XLSX.

[PaigeMiller]

---

@Tecla1 wrote:
Good Morning....
Tnks fo your replay... I receive this error:
ERROR: Insufficient authorization to access /Totale_campioni_polizze_addebiti__&des..XLSX.

---

This is why you ALWAYS need to include the log when you have code the doesn't work. There's no way we could have guessed from your code and extremely brief description that this is the problem. And we want not just the error message, but the code in the log that precedes the error message, you need to show us at least one PROC or DATA step in the log before the error message.

I think @Quentin has the right answer.

[PaigeMiller]

Posting code that doesn't work, without a log, is usually not productive. ALWAYS show us the log. And we need to see not just the error messages; we need to see the preceding code as well, covering one whole PROC or DATA step before the error message.

Also, the description of the problem is way too brief: "I'm not able in any way to put more sheet in the same file." 

[Tecla1]

Tnks to all for your kindly replay.

I think that the problem is that the excel file is closed after the first export.... and the "ods excel file" open a new file fo the second sheet and lost the data.... 

The routine read from the list table two time for the same file and the two sheet has various options....  

But I can put shet option only with "ods excel".... (is not possible with proc export...)

It's a big problem..... 

I don't want to open and close file two time for each "DIREZIONE" .... 🙄