Data Limitations

[gblack686]

I have data that is 25 columns by 15 billion rows. I am having a hard time wrangling the data... 

1. What is the best practice for Dropping 21 columns? It's taking around 6 hours. 

2. Is there an EMR process in SAS?

3. How do I truncate the size of the file on import?

[SASKiwi]

15 billion rows is definitely moving into Big Data territory. However I think we need to understand your requirements and your SAS environment in more detail before providing useful advice.

Can you describe your SAS infrastructure - what version and maintenance release of SAS are you using on what OS? How many cores and how much memory does your SAS Application server have? Where is the 15 billion row table held? In an external database, flat files, SAS dataset or some other form?

Please post the SAS log of the program you are using to process the table.

If you only need four columns from this table, then only read these columns when extracting. No need to drop later.

[Kurt_Bremser]

What type are the columns? If they are all numeric, a quick calculation gives me a physical dataset size of ~ 2.2 TB and a rather mediocre data throughput of just 100 M/sec, which means your storage infrastructure and/or your connection to the data source (in case of external DBMS) is not scaled correctly for the size of your data.

[ballardw]

---

@gblack686 wrote:

I have data that is 25 columns by 15 billion rows. I am having a hard time wrangling the data... 

1. What is the best practice for Dropping 21 columns? It's taking around 6 hours. 

2. Is there an EMR process in SAS?

3. How do I truncate the size of the file on import?

---

What do you intend by "dropping 21 columns"? If you mean permanently remove the variables I would typically suggest that if you never ever need the variables to not read them in the first place. Which may depend on your file source how the best way would be. It might help to show the code you are currently using to "drop" the columns that is taking that long.

If you might need to use those variables at some time you do not need to remove columns but use data set options to keep the needed variables for a specific task. There will be some time to process 15 billion rows but I suspect something else in the performance is an issue. Such as is the source data in another DBMS like Oracle or SQL Server? Then some tuning of the code used and perhaps requesting only the variables you need is in order.