BookmarkSubscribeRSS Feed
Obsidian | Level 7

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";

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...

Super User

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:


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. 




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  noprint;
  var invoice;
  histogram invoice /
    midpoints=(0 to 180000 by 5000)    
ods trace off;
Obsidian | Level 7

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? 


file='D:\qsilver\SAS Data\BZ\3_Output\SAS_Output.xls'
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)    
ods tagsets.excelxp close;
Super User

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.

Obsidian | Level 7
ods graphics / height=400 width=800 noborder;
proc sgplot;
histogram msrp;

Use this one instead 

Super User

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?





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;
   var Weight;
   histogram Weight / endpoints=(0 to 8000 by 1000) 
                      barlabel=count outhist=BinCount;
proc print data=BinCount; run;



Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.


Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 


Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 6 in conversation