SAS programming concepts in this and other Free Data Friday articles remain useful, but SAS OnDemand for Academics has replaced SAS University Edition as a free e-learning option. Hit the orange button below to start your journey with SAS OnDemand for Academics:
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.
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.
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:
Did you find something else interesting in this data? Share in the comments. I’m glad to answer any questions.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.