While I've often written about how to get your SAS data to Microsoft Excel in some automated way, I haven't really addressed what's probably the most frequently used method: copy and paste. SAS Enterprise Guide 7.1 added a nifty little feature that makes copy-and-paste even more useful.
The new "Copy with headers" feature creates a tab-delimited version of your selected data cells, complete with a heading row that includes the column names. This is very convenient for copying into a new Microsoft Excel spreadsheet or other table structure (like a Google Docs spreadsheet). Note: this is different than the "copy data attributes" tip that I published a while back. That tip captures the column properties, but not the actual data values.
To get started, simply select the data that you want within the SAS Enterprise Guide data grid. The data cells must be contiguous, but you don't need to select all columns or all cells within the data set. With the selection active, right-click and select Copy with headers.
This action places the data values in tab-delimited form onto the Windows clipboard. The first line of the data will be the SAS variable names from your selected data. If you paste this into a text editor that shows a view of "special characters", you can see the tabs along with the end-of-line delimiters.
When you paste the same content into Microsoft Excel, the Excel application knows how to automatically distribute these values into distinct columns. That's just what spreadsheet programs do.
Microsoft Excel isn't the only app that can handle tab-delimited data in this way. You can paste the content into a Google Doc spreadsheet too.
Or, you could simply paste into a text file as-is, and then save that file to read into a SAS program at a later time, bringing it full circle.
10 Comments
It amazes me how many work arounds people at SAS come up with in order to get people to use SAS EG. SAS EG is terrible. It's clunky and cumbersome. If this is the wave of the future for SAS, the future doesn't look good.
SAS Enterprise Guide is a popular app for SAS users who want to be productive with programming and other SAS tasks. That includes me -- I use it every day to analyze our blog and online communities data, among other projects. But it's not everyone's favorite way to use SAS. New learners are cutting their SAS teeth on SAS Studio, part of the SAS University Edition. SAS still supports SAS display manager, with all of its windows and keyboard shortcuts, for those that prefer the classic view of SAS. SAS is famous for providing its customers with options for all skill levels and disciplines, so find a method you like and dig in.
Thanks for your response.
I'm being FORCED to use SAS EG and it's actually slowed down my production.
I began programming with server SAS. When PC SAS came out it was great. It provided the user with options that users wanted and actually used to be more productive with its interaction with PC tools.
I've talked with my peers and SAS EG is not seen as anything but a step backwards in analytics. It's lost functionality and ease of use. I've been a big supporter of SAS since I started. I cannot get behind SAS EG as anything but Microsoft Access on steroids.
I'm sorry to hear that your work environment has limited your choice of tools. Because EG is simply a client app that connects to SAS and your data, support for interaction with PC tools can be limited. For example, it's why you can't use DDE in SAS to interact with Excel when SAS is on a server. That's not an EG limitation per se; it's a result of an IT policy to centralize the compute and data server away from desktop computers. With cloud computing upon us, this "separation of concerns" is only going to grow, and having everything (data, applications, and compute power) colocated on a desktop PC will become more rare in big enterprises. I'll admit it: I sometimes struggle with this new way of working -- but it's where we're headed.
Pingback: Copy SAS variable names to the clipboard in SAS Enterprise Guide - The SAS Dummy
Pingback: Ad-hoc reporting with SAS: Tips for the e-mail jockey - The SAS Dummy
I learned SAS in grad school way back in the 70's so I had punch cards at first ;-( Fast forward to last week now learning EG. It was very hard for me to embrace point and click but now after only one project I am ready to move on to the new stuff. It did slow me down to learn EG but now I feel like I can move faster with simple projects.
Please sign me learning the new stuff ;-)
Marty - that's great to hear! You should tune into the SAS Enterprise Guide discussion forum to keep learning that new stuff!
I hear ya Marty - as I also cut my programming teeth in the 70's using SAS! ....but I actually have a bit different story and take (spoiler alert: I've come to basically hate all SAS, EG and otherwise, no matter how hard I try to like it again!). After major programming achievments using SAS back in the 80's, including a complex dissertation using SAS/IML, I counted myself back as a SAS guru/expert. And then I began a long career in Marketing Research, where everyone uses SPSS....so I migrated over to SPSS. I used in extensively daily, though occasionally (< 5% of time) used special macros only available in SAS. SPSS has some amazingly elegantly simple functionalities that leave SAS in the dust. I could quickly throw data into SPSS use Aggregate to do all dorts of distilling and be back in Excel for reporting in like 30 seconds .... now (having to use SAS) it takes me 10 minutes plus I have to do all the ancillary stuff of Data step, save project (not data?) in EG, etc. etc. SAS now frustrates me to no end - though I used to be its biggest fan. Odd. But I am certain for 99% of the tasks I do (including high end modeling) I can do in SPSS (using both point/click and code) in half to 75% of the time what it takes to do same in SAS. In fact, I'd love to se some head to head "speed and ease" competitions....I'll be first to sign up!
Thanks for the comment, Frank. I find that productivity in a tool is heavily influenced by exposure and practice, plus access to the right capabilities for the job at hand. I use EG for similar tasks that you describe -- almost daily -- and I get them done pretty quickly. But...I really know the tool and the tricks that help me, and that's what I try to share here. We also have a tremendous community that helps learners to become productive. I agree that Marketing Research is a niche where SPSS is popular, although I've worked with folks who developed some good practices with SAS tools.
I'll leave the head-to-head exercises for other folks to figure out. For me, the key is flexible tools, a powerful programming language, and proper training and practice.