ODS Export Format

[trevand]

I am exporting output from proc means to excel using ODS and the output looks like this:

year  month variable mean
2018      1          var_1      0.5
                           var_2     0.6
                           var_3     0.3
               2          var_1      0.4
                           var_2     0.9
                           var_3     0.7

But I would like to look like this:

year  month variable mean
2018      1         var_1      0.5
2018      1         var_2     0.6
2018      1         var_3     0.3
2018     2         var_1      0.4
2018     2         var_2     0.9
2018     2         var_3     0.7

Here is the code that I am using:

ods excel file=example.xlsx;
proc means data=example STACKODSOUTPUT mean;
    class year month;
    var var_1 var_2 var_2;
run;
ods excel close;

[ballardw]

One way is to create an output data set then display that with proc print:

proc means data=example STACKODSOUTPUT mean ;
    class year month;
    var var_1 var_2 var_3;
    ods output summary=mysummarydata;

run; 

ods excel file="filename.xlsx";

proc print data=mysummarydata noobs;
   var year month variable mean;
run;

ods excel;

The ODS OUTPUT creates a different structured data set than the OUTPUT statement making one that looks more like the stackods output but with the year and month on each observation. There will be other variables in the data set as well by default so the VAR statement in Proc Print just lists the ones you want.

May want to set a format for the Mean variable to control the number of decimals in the Proc Print output.

[Tom · Accepted solution]

If you want the data out of PROC MEANS then ask for it.  No need to try to treat the PRINTOUT that it produces as if was DATA.

proc means data=example nway noprint;
  class year month;
  var var_1-var_3;
  output out=means mean=;
run;

Result

Obs    year    month    _TYPE_    _FREQ_    var_1    var_2    var_3

 1     2018      1         3         1       0.5      0.6      0.3
 2     2018      2         3         1       0.4      0.9      0.7

If you want it transposed add a PROC TRANSPOSE step.

proc transpose data=means name=variable out=tall(rename=(col1=mean));
  by year month;
  var var_1-var_3;
run;

Result

Obs    year    month    variable    mean

 1     2018      1       var_1       0.5
 2     2018      1       var_2       0.6
 3     2018      1       var_3       0.3
 4     2018      2       var_1       0.4
 5     2018      2       var_2       0.9
 6     2018      2       var_3       0.7