topic Re: exporting proc summary report file to excel says file is corrupt in SAS Studio

exporting proc summary report file to excel says file is corrupt

bondtk — Mon, 06 Nov 2017 07:13:44 GMT

I am writing this code below to export the file to excel, it shows excel file in the folder , when I try to open it, it says file is corrupt.

please advise if there is any problem in the code.

ods excel file='/folders/myfolders/pg2/rawdatapivotnov.xlsx' ;

proc summary data=orion.aaamonthlydata nway missing;

class Amount_Financed System_decision lend_level;

output out= rawdatapivotnov;

run;

ods excel close;

I am running SAS Studio via Oracle VM Virtual box manager which I believe is Linux based, my laptop is windows based, is it a conflict of different environment that caused this excel file as corrupt, please advise.

Re: exporting proc summary report file to excel says file is corrupt

RW9 — Mon, 06 Nov 2017 09:28:09 GMT

To be honest, and I haven't checked this, I didn't think proc summary created any ods destination output when run. Hence nothing is going to the xlsx file. This line:

output out= rawdatapivotnov;

Says to create a dataset with the output, so maybe after that summary you want to have a proc report step and report out that dataset that was created so some actual data is written out to the xlsx file.

Re: exporting proc summary report file to excel says file is corrupt

Kurt_Bremser — Mon, 06 Nov 2017 12:06:29 GMT

By default, proc summary does not create output; proc means, OTOH, does.

Re: exporting proc summary report file to excel says file is corrupt

bondtk — Tue, 07 Nov 2017 04:19:09 GMT

Hi, when I run this code, no error comes, it says 8011 observations read from original data set, work.rawdatapivotnov has 5297 oberservations and 5 variables, excel file is written. But when I open it. It has error. So not sure what happened,

its ts quite easy to export proc summary in SAS EG as it gives the export option, so not sure why SAS studio has no option to export to excel file.

Re: exporting proc summary report file to excel says file is corrupt

bondtk — Tue, 07 Nov 2017 05:27:20 GMT

Hi RW9

ODS doesn't work for me so I run the proc summary first and then use the proc export after that and it worked.

/*code for proc summary */

proc summary data=orion.aaamonthlydata nway missing;
class Amount_Financed System_decision lend_level;
output out=orion.volumedist;
run;

/* export proc summary to excel file */

proc export
data=orion.volumedist
dbms=xlsx
outfile="/folders/myfolders/pg2/rawdatapivotnov.xlsx"
replace;
run;

Re: exporting proc summary report file to excel says file is corrupt

bondtk — Tue, 07 Nov 2017 05:28:20 GMT

Hi Kurt,

ODS doesn't work for me so I run the proc summary first and then use the proc export after that and it worked.

/*code for proc summary */

proc summary data=orion.aaamonthlydata nway missing;
class Amount_Financed System_decision lend_level;
output out=orion.volumedist;
run;

/* export proc summary to excel file */

proc export
data=orion.volumedist
dbms=xlsx
outfile="/folders/myfolders/pg2/rawdatapivotnov.xlsx"
replace;
run;

Re: exporting proc summary report file to excel says file is corrupt

Kurt_Bremser — Tue, 07 Nov 2017 06:00:20 GMT

Try your ODS code again, but with means instead of summary.

Re: exporting proc summary report file to excel says file is corrupt

bondtk — Tue, 07 Nov 2017 06:35:01 GMT

Hi Kurt

If I replace it with proc means then it creates a proc means report withStd, means , min, max and I can see the excel file, but that's not the output I need, I need the proc summary report to go as a excel file and then I can run the pivot tables on the excel file. Proc export allowed me to do that, so I am not sure how ods will export the proc summary as an excel file.

thanks for your patience.

Re: exporting proc summary report file to excel says file is corrupt

Kurt_Bremser — Tue, 07 Nov 2017 06:53:58 GMT

proc means and proc summary follow the same syntax and have the same functionality, their default settings are different.

Add the print option to the proc summary statement.

Re: exporting proc summary report file to excel says file is corrupt

bondtk — Tue, 07 Nov 2017 07:42:13 GMT

Hi Kurt

Proc means gives me a table which I cant use for further analysis as I would like to run pivot tables on the data in excel file.

Proc summary gives me the data set which I can use for my purpose. I added the proc print to proc summary.

thanks for your help.