Learning SAS? Welcome to the exclusive online community for all SAS learners.

export to excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

export to excel

How to export a Proc Summary output to Excel using SAS University Edition


Accepted Solutions
Solution
‎10-03-2016 12:30 PM
Super User
Posts: 17,837

Re: export to excel

Doh! @Ksharp is totally correct - the NOPRINT is definitely an issue. I notice your also creating an output dataset. If you want to output that to Excel add a proc print before the ODS CLOSE. 

 

Proc print data=bsc_active noobs label;

Run;

View solution in original post


All Replies
Super User
Posts: 17,837

Re: export to excel

You have a few options. 

You can capture the output in a table, but the format doesn't look like what you see in the SAS Results Window.

 

Or you use ODS Excel. In your case, ODS Excel is probably the easiest method.

 

ods excel file='/folders/myfolders/sample.xlsx' style=meadows;

proc summary data=sashelp.class;
run;

ods excel close;

You can then navigate to where you set up myfolders and see the XLSX file, or in SAS UE, navigate to File/Folders/Server pane and then to myfolders. Under this you should see the XLSX file. If you right click you can 'download' it to where ever you like.

Occasional Contributor
Posts: 6

Re: export to excel

Hello,



I tried your suggestions by adding the ods excel statements. The log shows the message-:

NOTE: Writing EXCEL file: /folders/myfolders/OpenData/BSC_Active_Non.xls





However when I tried to open the excel file I got an error: Any suggestions. Thanks Minoo28

[cid:image001.png@01D21B35.EF8149D0]




Super User
Posts: 17,837

Re: export to excel

Post your full code and log. 

Make sure the file is XLSX 

Occasional Contributor
Posts: 6

Re: export to excel

CODE:

ods excel file='/folders/myfolders/OpenData/BSC_Active_Non.xlsx' style=meadows;

proc summary data=opendata.month_over_month_demographic noprint;

where plan='Blue Shield' and status='ACTIVE';

where also Medicare_Status='NON-MEDICARE';

by Plan month_yr cvg_tier status medicare_status;

var enrollment;

output out=BSC_Active_Non (drop=_type_ _freq_)SUM=ENROLLMENT;

run;

ods excel close;



LOG:


1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
NOTE: ODS statements in the SAS Studio environment may disable some output features.
56
57 ods excel file='/folders/myfolders/OpenData/BSC_Active_Non.xlsx' style=meadows;
WARNING: Style MEADOWS not found; Excel style will be used instead.
58 proc summary data=opendata.month_over_month_demographic noprint;
59 where plan='Blue Shield' and status='ACTIVE';
60 where also Medicare_Status='NON-MEDICARE';
NOTE: WHERE clause has been augmented.
61 by Plan month_yr cvg_tier status medicare_status;
62 var enrollment;
63 output out=BSC_Active_Non (drop=_type_ _freq_)SUM=ENROLLMENT;
64 run;
NOTE: There were 231 observations read from the data set OPENDATA.MONTH_OVER_MONTH_DEMOGRAPHIC.
WHERE (plan='Blue Shield') and (Medicare_Status='NON-MEDICARE') and (status='ACTIVE');
NOTE: The data set WORK.BSC_ACTIVE_NON has 189 observations and 6 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


65 ods excel close;
NOTE: Writing EXCEL file: /folders/myfolders/OpenData/BSC_Active_Non.xlsx
66
67 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
79




Super User
Posts: 17,837

Re: export to excel

And this Excel file generates an error? Post the error. 

Remove the style statement or change it to meadow or seaside. 

Occasional Contributor
Posts: 6

Re: export to excel

This is the error generated by Excel: Thanks Minoo28



[cid:image001.png@01D21B3C.B05BCE40]




Super User
Posts: 17,837

Re: export to excel

Your image isn't showing up.

Solution
‎10-03-2016 12:30 PM
Super User
Posts: 17,837

Re: export to excel

Doh! @Ksharp is totally correct - the NOPRINT is definitely an issue. I notice your also creating an output dataset. If you want to output that to Excel add a proc print before the ODS CLOSE. 

 

Proc print data=bsc_active noobs label;

Run;

Super User
Posts: 9,681

Re: export to excel

Use proc means instead of proc summary which would not 
give you any output.

And don't use NOPRINT option ,it would suppress any output either .


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 745 views
  • 1 like
  • 3 in conversation