How to handle large data

[dsbihill]

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;

[Kurt_Bremser]

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.

[Reeza · Accepted solution]

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;

[Ksharp]

I prefer to Data step + Array .