topic Re: Fastest way to calculate the max of a variable in a large dataset in SAS Programming

Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 08:58:55 GMT

Hi,

I have a dataset with tens of millions of lines and I wand to calculate the max of a column.

calculating this max took more than 10 minutes with proc sql and proc means.

Is there a fastest way to do this (Arrays, hash objects, iml)

thanks in advance

Re: Fastest way to calculate the max of a variable in a large dataset

Kurt_Bremser — Wed, 03 Feb 2016 09:07:49 GMT

If you need to find different max values for groups, there are several methods that may differ in performance.

But if you just need the overall max of a column, all thats SAS does is one sequential scan through the data set, which is only limited by the I/O throughput of your computer.

No matter if you do

proc sql;
select max(columna) from have;
quit;

data want;
set have (end=done);
retain maxa 0;
maxa = max(columna,maxa);
if done then output;
run;

or max with proc means.

Just do a quick calculation of (physical size of the dataset) / (runtime in seconds).

If your dataset contains lots of mostly empty strings or other repeating patterns, using the dataset option compress=yes might save you lots of disk space and therefore lots of time spent doing I/O.

Re: Fastest way to calculate the max of a variable in a large dataset

Ksharp — Wed, 03 Feb 2016 09:51:45 GMT

Data step is good enough .

data _null_;
 set sashelp.class end=last;
 retain max;
 max=max(max,weight);
 if last then put max=;
run;

Re: Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 09:52:52 GMT

Hi @Kurt_Bremser,

Thanks for your answer.

I'am aware that the I/O is the main reason of the important execution time. That's why i'am looking for ways that load partially or totally data into memory.

I used this program but still have the same execution time (cpu time 1:38 // real time 11:42).

data want (keep=max);
     set have (keep=variable) end=eof;
     array v{1}  variable;
     max=v{1};
     do i=2 to dim(b);
          if max<max(v{i}) then max=max(v{i});
     end;
     if eof;
run;

For your suggestion of using compress option, I'm using the data step keep= option, which I think more efficient.

best regards,

Re: Fastest way to calculate the max of a variable in a large dataset

user24feb — Wed, 03 Feb 2016 10:21:10 GMT

10 minutes computing time is somewhat strange.

I don't think hashing would be faster to get the max value than proc means or so. Is there something special about your data set?

Options Fullstimer;

Data A;
  Do i=1 To 1e7; * 10 million rows;
    x=Rannor(1);
	Output;
  End;
Run;

Proc SQL;
  Select Max(x) From A;
Quit;

Proc Means Data=A Max;
  Var x;
Run;

Data _NULL_;
  Length x 8.;
  If _N_ eq 1 Then Do;
    Declare Hash H (Dataset:'A (Keep=x)',Hashexp:20);
	Declare HIter HI ('H');
	H.Definekey('x');
	H.Definedone();
    Call Missing (x);
  End;
  Retain Max;
  rc=HI.First();
  Do While (not rc);
    Max=IfN(x gt Max,x,Max);
    rc=HI.Next();
  End;
  If rc Then Put Max;
Run;

Re: Fastest way to calculate the max of a variable in a large dataset

Kurt_Bremser — Wed, 03 Feb 2016 11:49:26 GMT

As long as you want to get the max of a column over the WHOLE dataset, there's no way around reading the WHOLE dataset. Period.

It's part of the way a SAS dataset is stored. You find all columns in all dataset pages, so thw whole shebang needs to be read.

The most important things are:

- reduce the physical storage size (if compress= has a noticable effect)

- make sure the dataset is only read once (no implicit sorting etc, happens often with proc sql)

If you find that you only need a subset of columns in most of your analyses, it makes sense to build a (horizontally) reduced dataset.

The same is true if you only need a special group of observations most of the time.

Re: Fastest way to calculate the max of a variable in a large dataset

Kurt_Bremser — Wed, 03 Feb 2016 11:54:09 GMT

If he/she has "tens of millions" of lines:

ex. 50,000,000 lines of 500 bytes each will lead to ~25 GB of data, or a throughput of 2.5 GB per minute

2500/60 gives a sustained throughput of ~40 MB/sec, which is not that bad from a typical PC hard disk.

With SAS it makes sense to invest in fast storage, SSD's preferred nowadays.

Re: Fastest way to calculate the max of a variable in a large dataset

Rick_SAS — Wed, 03 Feb 2016 12:48:08 GMT

What version of SAS are you using? How much memory have you allocated to your SAS process? Please post the SAS log that results from running the following:

%put SYSVLONG = &SYSVLONG;
proc options option=memsize value;
run;

Re: Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 13:12:32 GMT

Hi @Rick_SAS,

here is the output of the log (it's a french version):

SYSVLONG = 9.04.01M3P062415
26 proc options option=memsize value;
27 run;

SAS (r) Proprietary Software Version 9.4 TS1M3

Informations sur la valeur de l'option SAS MEMSIZE
Valeur :14014924800
Etendue : SAS Session
Comment la valeur d'option est définie : Config File
Nom du fichier de config. :
C:\Program Files\SASHome\SASFoundation\9.4\nls\fr\sasv9.cfg

Re: Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 13:26:27 GMT

Hi @Kurt_Bremser,

Thanks for your answer.

I think you're right about calculating the max of a variable over the WHOLE dataset. And here the difference is in the number of columns and their size (many character variables with length >20 caracters for example).

I created a table with only the variable on which I want to calculate the max.

data variable_only;
set have (keep=variable);
run;

creating the table with the dataset option keep=variable took approximately the same time of the max calculation. Although I have a SSD disk, the operation was very slow (let's say ~5mb per second)

After this, I computed the max using sql :

proc sql;
    select max(variable)
    from variable_only;
quit;

and it took only few seconds.

So It's working with data containing all variables that makes it slow, even when working with keep= or equivalent...

Re: Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 13:30:45 GMT

hi @user24feb,

thanks for your program and your effort.

here the fastest way is proc sql. hash table is slower due to loading data into memory.

Doing the calculation with a dataset with only one variable is really fast. I think as I mentioned in an other comment, the problem appears when working with a large dataset with many variables (especially character variable).

Re: Fastest way to calculate the max of a variable in a large dataset

Rick_SAS — Wed, 03 Feb 2016 13:37:47 GMT

OK, so you have the latest version of SAS (SAS 9.4m3) and 13GB of RAM. If you had a data set that has 10 million rows and ONE variable, computing the max would take a fraction of a second. As others have said, the issue is probably that you have a large number of variables and SAS has to read through the nonessential data to reach the numerical variables that it needs.

For our understanding, could you share:
1) How many total variables in the data set?
2) How many are character?
3) How many variables are you computing the max for?

Re: Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 13:50:17 GMT

@Rick_SAS,

1) 86 variables

2) 24 character variables

3) only one variable.

Yes, I totally understand this, and I think that's why it's kind of frustrating, Because if there was less variables, it would be very fast !

If there's a solution I think it can be more than a solution for this particular situation, but also probably a best practice when working with large data.

Re: Fastest way to calculate the max of a variable in a large dataset

Kurt_Bremser — Wed, 03 Feb 2016 13:50:45 GMT

majdi_ka wrote:

......

I created a table with only the variable on which I want to calculate the max.

data variable_only;
set have (keep=variable);
run;

creating the table with the dataset option keep=variable took approximately the same time of the max calculation. Although I have a SSD disk, the operation was very slow (let's say ~5mb per second)

After this, I computed the max using sql :

proc sql;
    select max(variable)
    from variable_only;
quit;

In the first step, you once again need to read the whole dataset. Writing the new dataset is a drop in the ocean compared to the cost of reading the big data. Although the keep= option looks as if you only read a small fraction of the data, the system still has to access the whole dataset.

In SAS, data is stored side-by-side in records, and the records consecutively, just as if you write it in a text file. RDBMS's will load and access data in a way that, when you access a single column, only that data is moved in memory; SAS can't do that with SAS tables.

Now, once you have the small dataset, you see how the max() operation improves by orders of magnitude.

Since you mentioned the presence of several character fileds with >20 characters, storing the big dataset with the compress= option is a must, IMO. Do that (if you haven't already), and then rerun all your operations to see how it changes performance.

Re: Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 14:15:10 GMT

This is very clear thanks.

In fact I didn't compress it yet.

I use this table to do joins. That's why I sorted it and created an index on it.

I don't know if the index still work if I compress it. In addition, I'm not sure if the dataset should be uncompressed each time I work on it, so it takes more time...

I will compress it and tell you the result !

Thanks

Re: Fastest way to calculate the max of a variable in a large dataset

Kurt_Bremser — Wed, 03 Feb 2016 14:38:23 GMT

You need never uncompress a SAS dataset that was created with compress=yes. SAS will always decompress it on the fly. The CPU cycles needed for umcompress are always offset by the

Caveat: when a compressed dataset is sorted, the utility file is not compressed and will need extra space; this can be mitigated by using the tagsort option in proc sort.

Once you recreate a SAS dataset with compress=yes, you also need to recreate the index, as the physical location of observations within the dataset file has changed. IIRC, as I do not use index files (we rarely need only small subsets of files, we usually work with whole datasets or large parts thereof, where indexes do not improve performance, but rather make it worse).

Re: Fastest way to calculate the max of a variable in a large dataset

Rick_SAS — Wed, 12 Sep 2018 18:51:26 GMT

Editor's note: this is a popular topic so we're summarizing here - use PROC MEANS, asking for MAX and name the one variable in the VAR statement. Even if the table is in a non-SAS database, the summary work should get pushed the database for fast processing.

Proc Means Data=MixTypeData Max;
  Var x1;
Run;

--- Details ---

So that everyone has the same data set to experiment on, here is a data set that has 10 million records, 86 variables, and 24 character variables of length 30 characters. On my CPU, it takes 30 seconds to create the data and 4 SECONDS to compute the mean.

@majdi_ka Please run this program. If it is taking "more than 10 MINUTES" to run, then I favor @Kurt_Bremser's theory that it is hardware related. I am not a hardware person, but others on the list might help you to investigate hardware/network issues like

Is your drive is fragmented/congested/slow/etc.
Is the data set on a mounted or networked drive? (Network access is slow.)
Is the data stored in a non-native format so that SAS is reading the data by using CEDA? Also slow.

%let nCont = 62;             /* number of contin vars               */
%let nChar = 24;             /* number of character vars         */
%let N = 1e7;
data MixTypeData;   
drop i j;
array x{&nCont} x1-x&nCont;  
array c{&nChar} $30. c1-c&nChar;  
call streaminit(1);
do i = 1 to &N; 
   do j = 1 to &nCont;              
      x{j} = rand("Uniform");  
   end; 
   do j = 1 to &nChar;
      c{j} = "ABCDEFGHIJKLMNOP";
   end; 
   output;  
end;  
run;

Proc Means Data=MixTypeData Max;
  Var x1;
Run;

Re: Fastest way to calculate the max of a variable in a large dataset

Kurt_Bremser — Wed, 03 Feb 2016 15:12:01 GMT

Rick, when you do that, you profit from the system's disk cache. If you do PROC MEANS immediately after creating the data set, most (if not all) of the file will still be in main memory.

I'm currently running a test on my (seriously old) server to get some values with different methods & settings.

(SAS 9.2, AIX 5.3 on p520, 2GB fibre channel SAN, Internal Ultra SCSI LVD disks)

Test1:

File created on SAN storage, program unchanged

File size ~12 GB

Creation: 4min 24 sec

Means: 1min 35sec

Test2:

compress option set, SAN storage

File size ~9.5 GB

Had to stop tests at this point, because some of my powerusers decided to run some real heavy-duty jobs and brought the server under severe load. WIll update once I have the server for myself.

Re: Fastest way to calculate the max of a variable in a large dataset

majdi_ka — Wed, 03 Feb 2016 15:41:46 GMT

I ran the program and It took 50 seconds to create the table and 6 seconds to calculate the max.

As I mentioned in a previous comment, I have 200 million lines and not 10 millions.

I tried on a subset of 50M and it took 2 minutes 30 seconds.

I then tried the compress option when creating a compressed version of the 50 M table. It took 1:20 which is a good improvement.

So the solution is mainly related with the size of the data. I store the SAS data from an oracle database. so every numeric data has a length of 8.

I will think of taking less variables from the original table, and define a smaller length for numeric variables (3 for flags for example).

then I will add a compress=yes (or binary or char, I must reread some papers about this). and then sort and create necessary indexes.

I think this will save disk space and execution time.

Thank you for your help and effort! Really useful advices 🙂

Re: Fastest way to calculate the max of a variable in a large dataset

LinusH — Thu, 04 Feb 2016 07:00:50 GMT

Let us step back and think of why do we optimize. If this is not a very frequent query and/or it does not hinder you to the analysis you wish, it's "just" annoying and something you could live with.

On the other hand, if this is a frequent request, perhaps required by many users, I think that you need to step up the game.
-are you on the right platform, sizing may be required
-are you using the right data engine? SPDE let you read in parallel. If you wish to optimise aggregate functions investing in SPD Server could be an option, since it support query aggregation on indexes (hence - no table scan). Or an extrnal RDBMS (perhaps a columnar one base on the query of your post). Other options that comes in mind is in memory databases (LASR with Visual Analytics ).