BookmarkSubscribeRSS Feed
0 Likes

Sometimes I want to interrogate data down in the murky depths of a large table (e.g. >100k rows).

Unfortunately SAS EG only lets me see 10,000 rows.

I'm not aware of an obvious option/setting to make this bigger.

I'm forced to export the data into an external file before I can view all the data. This is an unnecessary click-fest. Especially when I am trying to interrogate multiple large tables (e.g. if i am debugging/sense-checking/quick-checking/verifying). I just want SAS to show my rows 10,001 to N as I scroll down to the bottom of the table.

 

Also, on a similar vein, I want to quickly copy-paste more than 10,000 rows of data from SAS EG to a spreadsheet/text editor. I don't want to go through the SAS-right-click-and-select-export-method-from-menu-just-cos-i-have-10000-plus-rows feature (because it is a click-fest). A quick Ctrl-c, Ctrl-v is a lot faster and convenient especially if the copy-paste is just a once-off, and I want to do many Ctrl-c, Ctrl-v on a few tables to do some quick checking.

 

Incidentally, when selecting data, ctrl-c should copy data and ctrl-shift-c should copy data with column headers in my opinion.

 

Also, when viewing a large table in SAS, click-holding the scroll bar conveniently tells us "Row X of N". Except, when N is a large number, it tells us "Row X of 800+" or "Row X of 1650+". That's rather uninformative. I'd like to know how big N is. Why don't we just permanently display N somewhere on a status bar in SAS EG. I am aware you can right-click the table, click [Properties], click [Advanced] and see the number of [Rows]. Except, for large N, the [Rows] displays [Unknown], which is also unhelpful and in addition, right-clicking is just another click-fest if you are doing quick row size checks for multiple tables.

6 Comments
Reeza
Super User

SAS is more designed for you to write some code to check things, working with large data in Excel isn't fun and prone to mistakes. Often when checking something someone's done in Excel, there's a problem in Excel. 

 

You should be able to right click a dataset and open in Excel the last time I checked though. 

_Asdfghjkl
Fluorite | Level 6

Sometimes, quick one-off checks can be done faster in Excel instead of writing code. Being able to rapidly copy-paste data into Excel/notepad is a useful feature for various purposes.

 

Right-click a dataset and using [Send To] > [Microsoft Excel] only works up to 10,000 rows. If you have 20k rows in the dataset, it cuts out at 10,000 rows.

 

Dumping 20k rows requires you to right-click dataset, [Export] > [Export xxx as a Step in Project...] to dump 20k rows.

_Asdfghjkl
Fluorite | Level 6

I withdraw my statement regarding 10k row limit.

 

I subsequently found the 10k row display limit is set by:

 

[Tools] > [Options] > [Data] > [Performance] and set [Maximum number of rows to display in the data grid with SAS/ACCESS data:] to [No Limit]

 

Incidentally this also seems to control the [Send To] > [Microsoft Excel] feature when the dataset isnt in the WORK folder.

 

A bit more experimenting also shows me ctrl-c, ctrl-v only copy pastes the displayed data in the dataset (not the full dataset). So I am forced to use the [Send To] or [Export] features in SAS. It would be nice if ctrl-c, ctrl-v grabs all the dataset rather than only whats displayed.

Kurt_Bremser
Super User

As an addendum: send to Excel is extremely non-performant. It actually opens Excel in the background and writes to the spreadsheet cell-by-cell. Exporting to a file and opening that in Excel runs circles around "send to".

Edit: fixed typos.

ChrisHemedinger
Community Manager

The default 10K limit applies to database tables only (those accessed via SAS/ACCESS library engines), and not native SAS data sets.   As you found, you can adjust that limit upward at the potential risk of slower performance or more impact on the database.

 

Send To->Excel is convenient, but can be slow as @Kurt_Bremser says.  Export to Excel is faster.  

 

Don't forget the built-in Where command bar in the data grid -- it can be very effective for quick data checks.

ChrisHemedinger
Community Manager
Status changed to: Suggestion Implemented

As the Tools->Options values can control how much data you can see in the data view, that part is considered Implemented.  As far as putting the entire data set on the clipboard -- beyond what you can see in the grid view -- that's probably not realistic (as it could easily consume all memory and generate some nasty exceptions).