exporting proc summary report file to excel says file is corrupt

[bondtk]

Hi

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.

[RW9]

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.

[bondtk]

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.

[bondtk]

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;

[Kurt_Bremser]

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

[bondtk]

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;

[Kurt_Bremser]

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

[bondtk]

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. 

[Kurt_Bremser · Accepted solution]

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.

[bondtk]

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.