turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How to handle large data

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2014 12:24 AM

Hello I am working with a very large data set somewhere in the 50 million + records. The code that i have attached works ok when i subset out a month but will not work on a much larger chunk of the data.

What is the best way for me to be able to do this on the entire dataset consisting of 5 years?

Is sorting and then using data step my best option?

Thank you so much for all your expertise.

proc sql;

create table ambient_temp_dc as

select RNH, RN, datepart('occur date'n) as date, hour('occur date'n) as hour;

avg(basic_AT) as avg_ambient

from rmdeoap.gets_dw_eoa_flt_dccca_v

where RNH = 'CSX' and 'occur date'n ge '01Nov2014'd

group by RNH, RN, date, hour

;

quit;

Accepted Solutions

Solution

11-27-2014
02:32 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

11-27-2014 02:32 AM

50 million records isn't that big so there are various ways to handle this. I'd start off by trying a standard proc means even without the sort, though you may need it.

You can use formats, datetime12. to get the data by hour rather than calculate the hour/date and then separate them out afterwards if really desired.

If none of the above solutions work, you can always process the data a month at a time.

**proc** **means** data=rmdeoap.gets_dw_eoa_flt_dccca_v

(where = (RNH='CSX') keep=RNH RN 'occur date'n basic_AT) noprint;

class RNH RN 'occur_date'n;

ways RNH*RN*'occur_date'n;

format 'occur_date'n datetime12.;

output out=ambient_temp_dc mean(basic_at)=avg_ambient;

**run**;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dsbihill

11-27-2014 01:25 AM

Try to sort your existing dataset first.

proc sort

data=rmdeoap.gets_dw_eoa_flt_dccca_v (

where=(RNH = 'CSX' and 'occur date'n ge '01Nov2014'd)

)

out=tempdata

;

by RNH, RN, date, hour;

run;

If that succeeds, the data step (or proc means) to build the average is a breeze.

Note that the where condition on the input data set reduces the size of the utility file built while sorting, so you may escape your disk full problem. proc sql has the nasty habit of throwing all the data into one big utility file and doing all its stuff there, causing a tankerload of competing disk accesses that tend to slow down systems to a crawl and often cause unnecessary disk space problems.

proc sort can be made faster and more stable by using UTILLOC= to assign a separate physical disk for the utility files.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

Solution

11-27-2014
02:32 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

11-27-2014 02:32 AM

50 million records isn't that big so there are various ways to handle this. I'd start off by trying a standard proc means even without the sort, though you may need it.

You can use formats, datetime12. to get the data by hour rather than calculate the hour/date and then separate them out afterwards if really desired.

If none of the above solutions work, you can always process the data a month at a time.

**proc** **means** data=rmdeoap.gets_dw_eoa_flt_dccca_v

(where = (RNH='CSX') keep=RNH RN 'occur date'n basic_AT) noprint;

class RNH RN 'occur_date'n;

ways RNH*RN*'occur_date'n;

format 'occur_date'n datetime12.;

output out=ambient_temp_dc mean(basic_at)=avg_ambient;

**run**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dsbihill

11-27-2014 08:11 AM

I prefer to Data step + Array .