We’re smarter together. Learn from this collection of community knowledge and add your expertise.

How to get your data out of SAS (and to your users!)

by Regular Contributor 3 weeks ago - edited 3 weeks ago by Community Manager (923 Views)

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.

 

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

 

How to go about getting SAS University Edition

 

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.

 

Get the data ready

 

When I downloaded the data, it looked like this:

 

1.png

 

I needed to do some manual cleanup, and the end result looked like this:

 

2.png

 

This is now going to be easier to manipulate in SAS.

 

The Results

 

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):

 

4.png

 

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:

 

6.png

 

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:

 

7.png

 

But most exciting (and extremely helpful in my case with the 115 surgeons), each group is in its own separate tab:

 

8.png

 

 

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:

 

9.png

 

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:

 

10.png

 

Click Analytics U, then select "Subscribe" from the Options menu.

 

Happy Learning!

 

 

 

Comments
by Super User
3 weeks ago

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;
by Trusted Advisor
3 weeks ago

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

by Regular Contributor
3 weeks ago

@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

by Super User
2 weeks ago

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

by Regular Contributor
2 weeks ago

@ballardw I think I'm going to have a great deal of fun playing with this :-)

 

Hope you have a good one!
Chris

by Super User
2 weeks ago

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

by Regular Contributor
2 weeks ago

@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

Your turn
Sign In!

Want to write an article? Sign in with your profile.