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.
The first obvious, at least to me, is the case where you don't want a shared document at all but want each client/ user/ whatever to have a document with only their data.
So if you have a data set with, for extending this example, the candidate name:
proc sql; create table candidatelist as select distinct candidate from work.import; quit; data _null_; set candidatelist; call execute('ods excel file="/folders/myfolders/'||Candidate||'.xlsx';); Call execute('proc print data = work.import noobs;'); call execute('where candidate='||quote(strip(candidate))||';'); call execute('var _all_; run; ods excel close;'); run;
ODS Excel statement is awesome and great for reporting...Nice work!
If you haven't seen it already, there are some other options you may want to investigate further as outlined in this handy SAS R&D tip sheet https://support.sas.com/rnd/base/ods/ODS_Excel_Dest_tips.pdf
Kind Regards,
Michelle
@ballardw - That is so cool, and may actually make my life easier for the file I was creating for work - rather than sending one big file to all the surgeons, I could send the specific surgeon their own file. Awesome.
@MichelleHomes - ...admittedly I have not used ODS Excel much, so I've printed out the tip sheet and will be posting it at my desk tomorrow. As usual, SAS puts out the best documentation 🙂
Have a great week and chat soon!
Chris
@DarthPathos after the first couple parts are working reliably you could then modify the code to have SAS send an email with the file as an attachment directly to the target audience.
@ballardw I think I'm going to have a great deal of fun playing with this 🙂
Hope you have a good one!
Chris
@DarthPathos you won't be the first person to have to split things up! I work with data quality reports where the overall program manager needs to see how the program units data looks and at the same time provide a feedback document to each program unit that does not have the other unit's data. And the program unit reports include summaries of each of their subunit that the overall program doesn't want to see...
@ballardw I'm seeing all sorts of possibilities for this - and here i was thinking I was going to be able to sleep tonight! 🙂 Can't wait to start playing with this tomorrow....
Chris
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.