topic Re: ODS EXCEL in New SAS User

ODS EXCEL

lewisnovack — Wed, 19 Dec 2018 17:18:43 GMT

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;

Re: ODS EXCEL

Reeza — Wed, 19 Dec 2018 17:30:01 GMT

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;

Re: ODS EXCEL

lewisnovack — Wed, 19 Dec 2018 17:58:43 GMT

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

Re: ODS EXCEL

Reeza — Wed, 19 Dec 2018 18:02:58 GMT

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

Re: ODS EXCEL

lewisnovack — Wed, 19 Dec 2018 20:55:18 GMT

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

Re: ODS EXCEL

ballardw — Wed, 19 Dec 2018 21:19:43 GMT

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

Re: ODS EXCEL

Vince_SAS — Thu, 20 Dec 2018 13:15:03 GMT

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

Re: ODS EXCEL

lewisnovack — Mon, 28 Jan 2019 17:56:57 GMT

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_

Re: ODS EXCEL

Reeza — Mon, 28 Jan 2019 20:38:23 GMT

Sure, just stack them that way.

data want;
set mm.result_: ;
run;