topic Re: Export Proc Means to Excel in SAS Programming

Export Proc Means to Excel

Pabster — Fri, 12 Apr 2019 16:34:34 GMT

Hi all!

It seems to be a very simple quetion but I can't get around it.
I have obtained data from my data set with Proc means.

proc means data=Z_score2;
var X1 X2 X3 X4 X5 Z;
by Costat SIC1_4;
run;

But now I would like to export that proc means data into an excel file automaticaly.

How can I proceed?

Re: Export Proc Means to Excel

ballardw — Fri, 12 Apr 2019 16:50:58 GMT

If you have the SAS/Interface to PC Files:

ods excel file="<your path>\<yourfilename>.xlsx"
;

proc means data=Z_score2;
var X1 X2 X3 X4 X5 Z;
by Costat SIC1_4;
run;

ods excel close;

is likely simplest.

If you don't have Interface to PC files the use ODS tagsets.excelxp instead of ods excel.

You will get complaints from Excel about the file format not matching as tagsets.excelxp creates XML instead of XLSX native formats but hopefully can open it.

Re: Export Proc Means to Excel

Reeza — Fri, 12 Apr 2019 16:53:05 GMT

There are many ways but I'll point out two methods:

1. Create an output data set and export the data set

2. Have your output directly sent to excel

1. PROC MEANS + PROC EXPORT

proc means data=Z_score2 stackods;
var X1 X2 X3 X4 X5 Z;
by Costat SIC1_4;
ods output summary = want;
run;

proc export data=want outfile='/folders/myfolders/demo1.xlsx' dbms=xlsx replace;run;

2. ODS EXCEL

ods excel file='/folders/myfolders/demo2.xlsx' style=seaside;

proc means data=Z_score2;
var X1 X2 X3 X4 X5 Z;
by Costat SIC1_4;
run;

ods excel close;

@Pabster wrote:

Hi all!

It seems to be a very simple quetion but I can't get around it.
I have obtained data from my data set with Proc means.
proc means data=Z_score2;
var X1 X2 X3 X4 X5 Z;
by Costat SIC1_4;
run;
But now I would like to export that proc means data into an excel file automaticaly.

How can I proceed?

Re: Export Proc Means to Excel

VDD — Fri, 12 Apr 2019 16:55:27 GMT

proc means data=Z_score2;
var X1 X2 X3 X4 X5 Z;
by Costat SIC1_4;
output out=results;
run;

PROC EXPORT DATA= results 
            OUTFILE= "\\path\results.xlsx" 
            DBMS=EXCEL REPLACE;
RUN;

Re: Export Proc Means to Excel

Pabster — Fri, 12 Apr 2019 17:23:04 GMT

@Reeza
Thank you very much the 2nd method is perfect!

by any chance do you also know how I could obtain all the data in ONE spreadsheet?

Because this proc means gives me around 100 different sets of data, as you can imagine trying to create graph afterwards might be a bit of a problem with 100tabs

Re: Export Proc Means to Excel

Reeza — Fri, 12 Apr 2019 17:26:00 GMT

In that case, I'd highly recommend the first option instead, it will give you a single table that's much nicer to use to create your graphs. In fact, you can easily graph that within SAS if needed as well.

Re: Export Proc Means to Excel

Reeza — Fri, 12 Apr 2019 17:26:59 GMT

Or you could modify it as:

ods excel file='/folders/myfolders/demo2.xlsx' style=seaside;

ods select none;
proc means data=Z_score2;
var X1 X2 X3 X4 X5 Z;
by Costat SIC1_4;
ods output summary=want;
run;
ods select all;

proc print data=want;
run;

ods excel close;

Re: Export Proc Means to Excel

Pabster — Fri, 12 Apr 2019 17:36:40 GMT

Yeah sadly I haven't reached the graph level in sas but I'll be looking into it soon! Thanks!

Re: Export Proc Means to Excel

ballardw — Fri, 12 Apr 2019 22:56:03 GMT

@Pabster wrote:

@Reeza
Thank you very much the 2nd method is perfect!

by any chance do you also know how I could obtain all the data in ONE spreadsheet?

Because this proc means gives me around 100 different sets of data, as you can imagine trying to create graph afterwards might be a bit of a problem with 100tabs

If by "100 different sets of data" you mean separate displayed tables for each combination of your variables on the BY statement you can try changing BY to CLASS. That will still provide summaries grouped by the variables but the results are a single table.

For example:

proc means data=sashelp.class;
   class sex age;
   var height weight;
run;

A secondary benefit is CLASS variables do not need to be sorted as BY variables do.

Differences in output data sets are available as you can get additional summaries of 1) data overall, 2) each level alone of each class variable, 3) if you have more than 2 class variables you'll also get the combinations of 2, or 3 or what ever smaller number of variables and 4) the combinations of the all levels of all the class variables.