Every so often, I learn something new in SAS that’s so simple and useful, it once again proves how powerful a tool SAS truly is. I work for a hospital in Toronto, Canada and one of my roles is the “Computer Geek” for the operating room – I’m responsible for hardware, software support, data analysis, database admin, etc.
I was asked to pull a list of our surgeons and the procedures they’re authorised to do; considering we have over 115 doctors, and over 900 procedures available, that was going to take a very long time. I realised that I could just provide a simple list, but that wasn’t going to work – the admin for Dr Jones doesn’t want to see Dr Brown’s procedures and vice versa. So, although I know how to get data into SAS, I’ve always copied and pasted what I needed; this adventure into exporting was exciting to me.
Get the data
I have friends that live in the town of Oakville (about 45 minutes outside of Toronto) and one is a huge advocate for open data. She was very impressed that Oakville’s recently been awarded the ISO’s 37120 Platinum Certification, which recognizes the town’s efforts at measuring performance and maintaining and expanding the Open Data program.
I figured it was a good place to find some data to use, and I was right. Oakville, like other cities in Ontario, is very devoted to the mayor – continuously re-electing incumbents. Some cities have the same mayor for a decade or longer. Voting results for the 2010 election are available here.
If you don’t already have University Edition, get it here and follow the instructions from the pdf carefully. If you need help with almost any aspect of using University Edition, check out these video tutorials. Additional resources are available in this article.
When I downloaded the data, it looked like this:
I needed to do some manual cleanup, and the end result looked like this:
This is now going to be easier to manipulate in SAS.
Assuming we’ve finished whatever analysis we needed and we want to export the data to Excel, I know most people use PROC EXPORT. In SAS University Edition, there’s a handy snippet ready for you that you can use. When I fill in my information about location and dataset, I get this:
proc export data=work.import outfile=_dataout dbms=csv replace; run; %let _DATAOUT_MIME_TYPE=text/csv; %let _DATAOUT_NAME=candidates.csv;
When I open the CSV, I get an Excel-based version of what I have in SAS (and, obviously, my original file):
It’s good, but not great – I can make some improvements. The trick is to use the ODS Excel statement, which opens the doors to all sorts of cool things (note that the location in the file= statement may be different for you):
proc sort data=work.import; by candidate; run; ods excel file="/folders/myfolders/Test.xlsx"; ods excel options (sheet_interval='BYGROUP' suppress_bylines="yes" ) ; proc print data = work.import noobs; by candidate; pageby candidate; var _all_; run; ods excel close;
Now when I run my ODS Excel statement, SAS generates this for me:
You’ll notice that the different candidates are now showing up under the table of contents, and on the right the Candidate’s name is at the top. When I go to my folder and open my Excel file, I get this:
But most exciting (and extremely helpful in my case with the 115 surgeons), each group is in its own separate tab:
Now it’s your turn!
Did you find something else interesting in this data? Share in the comments. I’m glad to answer any questions.
Need data for learning?
The SAS Communities Library has a growing supply of free data sources that you can use in your training to become a data scientist. The easiest way to find articles about data sources is to type "Data for learning" in the communities site search field like so:
We publish all articles about free data sources under the Analytics U label in the SAS Communities Library. Want email notifications when we add new content? Subscribe to the Analytics U label by clicking "Find A Community" in the right nav and selecting SAS Communities Library at the bottom of the list. In the Labels box in the right nav, click Analytics U:
Click Analytics U, then select "Subscribe" from the Options menu.