ODS EXCEL

[lewisnovack]

Hi Everyone,

I am running 20 simulations that return 1 line of information each. I am looking at taking these line (that returns in a separate tab of excel) and automatically exporting them into one excel document.

Currenlty I have:

ODS EXCEL FILE=" C:\Users\len676\Desktop\Simulation_Novack\Log[x]\A_log_gamma_results.xls"  /*style=normal*/;
ODS EXCEL options ( sheet_name="result 1");

    ODS EXCEL options ( sheet_name="Result_2");
    proc print data=MM.RESULT_2 NOOBS label ;run;

    ODS EXCEL options ( sheet_name="Result_3");
    proc print data=MM.RESULT_3 NOOBS label;run;
 
    ODS EXCEL options ( sheet_name="Result_4");
    proc print data=MM.RESULT_4 NOOBS label ;run;

    ODS EXCEL options ( sheet_name="Result_5");
    proc print data=MM.RESULT_5 NOOBS label;run;
 
    ODS EXCEL options ( sheet_name="Result_6");
    proc print data=MM.RESULT_6 NOOBS label ;run;

(this countinues to RESULT_20)

proc print data=RESULT_2 NOOBS label ;run;
ODS EXCEL CLOSE;
ODS LISTING;

[Reeza]

ODS Excel should use an XLSX file extension. And you didn't actually ask any question.

---

@lewisnovack wrote:

Hi Everyone,

I am running 20 simulations that return 1 line of information each. I am looking at taking these line (that returns in a separate tab of excel) and automatically exporting them into one excel document.

Currenlty I have:

ODS EXCEL FILE=" C:\Users\len676\Desktop\Simulation_Novack\Log[x]\A_log_gamma_results.xls"  /*style=normal*/;
ODS EXCEL options ( sheet_name="result 1");

    ODS EXCEL options ( sheet_name="Result_2");
    proc print data=MM.RESULT_2 NOOBS label ;run;

    ODS EXCEL options ( sheet_name="Result_3");
    proc print data=MM.RESULT_3 NOOBS label;run;
 
    ODS EXCEL options ( sheet_name="Result_4");
    proc print data=MM.RESULT_4 NOOBS label ;run;

    ODS EXCEL options ( sheet_name="Result_5");
    proc print data=MM.RESULT_5 NOOBS label;run;
 
    ODS EXCEL options ( sheet_name="Result_6");
    proc print data=MM.RESULT_6 NOOBS label ;run;

(this countinues to RESULT_20)

proc print data=RESULT_2 NOOBS label ;run;
ODS EXCEL CLOSE;
ODS LISTING;

---

[lewisnovack]

How can I export the results into one single excel sheet instead of individual tabs

[Reeza]

I would probably recommend combining your data and doing a single PROC PRINT then. 

This would combine it into one table, Results_All. Then use the approach above to print out this table. 

data Results_All;
set MM.RESULT_1 - mm.result_20;
run;

Another option is to set sheet_interval to none, which means all output goes to one file. 

ods excel file=' path to xlsx file' options(sheet_interval='none');

---

@lewisnovack wrote:
How can I export the results into one single excel sheet instead of individual tabs

---

[lewisnovack]

Thank you, unfortunately, it does not work here. It still displays in individual tabs within the excel document. 

[ballardw]

---

@lewisnovack wrote:

Thank you, unfortunately, it does not work here. It still displays in individual tabs within the excel document. 

---

You might show the actual code you ran from the ODS destination statement to the close. And if you did not close the original spreadsheet you may not have actually written a new one. Check the log for any warnings or errors. If you got any then post them along with the code.

[Vince_SAS]

PROC APPEND or the DATA step code that @Reeza posted should create a SAS table with all of the data.  Then use PROC PRINT with the final table.

proc append base=work.alldata data=mm.result_1; run; quit;
proc append base=work.alldata data=mm.result_2; run; quit;
proc append base=work.alldata data=mm.result_3; run; quit;
proc append base=work.alldata data=mm.result_4; run; quit;
proc append base=work.alldata data=mm.result_5; run; quit;
(this countinues to RESULT_20)

ods Excel file='C:\Users\len676\Desktop\Simulation_Novack\Log[x]\A_log_gamma_results.xlsx'
  options(sheet_name='All Data');

proc print data=work.alldata noobs label; run; quit;

ods Excel close;

Vince DelGobbo

SAS R&D

[lewisnovack]

Is there a way to do this without naming each data set individually. Can this be done automatically to any data set containing MM.Result_

[Reeza]

Sure, just stack them that way.

data want;
set mm.result_: ;
run;