Exporting Histogram Data to Excel

[bzubrick]

Hello I have the following code which i am usiung to create a histogram in SAS.

title 'Home Purchase Loans: June 2017 Distribution of Loan Amount from WAS';
proc univariate data=work.WASData_Purchase;
histogram AppAmount / midpoints=(50000 to 1500000 by 50000)  odstitle=title odstitle2="Midpoints";
run;

My boss wants to export the data into Excel instead of having it display in SAS and we can customize a few things. 

What is the best way to do this? Thank you in advance...

[Reeza]

A histogram just groups the data, so you can do the groups manually in a data step and export it. 

To create/bin your data, I'd recommend a format, proc freq to get the counts and export the data.

Or you can capture the ODS table:

http://blogs.sas.com/content/iml/2012/08/01/data-fro-ods-graphics.html

Alternatively you can export and edit the graph but I suspect he wants to create the graph in Excel as well. At that point (personally) you may as well export all your data and use Excels tools. 

Note that the default PROC UNIVARIATE type graphs are not graphically nice, but that SGPLOT will allow you to customize the graph and make cleaner looking production ready graphs. 

[BrunoMueller]

Hi

You can tell Proc UNIVARIATE to use ODS Graphics to create the output. Using ODS TRACE you can figure out which output objects are created. The output objects data can be written to a SAS data set using ODS OUTPUT.

Here is a code sample that illustrates the points mentioned above. It will only print the histgram plot and write all the data from the plot to a SAS data set.

ods graphics on;
ods trace on;
ods output Histogram=histodata;
proc univariate data=sashelp.cars  noprint;
  var invoice;
  histogram invoice /
    midpoints=(0 to 180000 by 5000)    
  ;
run;
ods trace off;

[bzubrick]

Hi thank you for your reply and providing Code I can adapt to my datasets. When I run this code, i get three outputs called panel1. 1 is HTML, one is SAS table, and one appears to be excel. I open the SAS table and it is exactly what I'm looking for. I want this data to export into a excel file with a specific name. I am trying to use the code below to do that, but the excel file it generates is blank. Any thoughts? 

ODS TAGSETS.EXCELXP
file='D:\qsilver\SAS Data\BZ\3_Output\SAS_Output.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );
proc univariate data=work.WASData_Refi  noprint;
  var appamount;
  histogram appamount /
    endpoints=(5000 to 1500000 by 5000)    
  ;
run;
ods tagsets.excelxp close;

[Reeza]

save the file with an XML extension instead and see if it works. Make sure to open it in Excel, but note that it's not a native Excel file, it's an XMLfile.

[Dunne]

ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
histogram msrp;
run;

Use this one instead 

[ballardw]

When discussing moving data between programs it is a good idea to describe the appearance of the data the receiving program wants.

Do you want a single row of data with a count for each bin and some way to identify the bin?

Do you want the bin and count in columns?

Something else?

[Rick_SAS]

If you want the bins and counts in a SAS data set (which you can then import to Excel), use the OUTHIST= option on the HISTOGRAM statement in PROC UNIVARIATE. There is an example in this blog post:

ods select Histogram;
proc univariate data=sashelp.cars;
   var Weight;
   histogram Weight / endpoints=(0 to 8000 by 1000) 
                      barlabel=count outhist=BinCount;
run;
proc print data=BinCount; run;