topic Re: How to find a max value while restricting number of rows in SAS Data Management

How to find a max value while restricting number of rows

jkurka — Fri, 12 Feb 2016 03:49:33 GMT

Hello,

I am trying to develop a code that find the maximum value in a range of 1800 rows and subsequently condenses the data down to that one row, and then move on to the next 1800 and do the same.

Overall I have about 30 data sets, each of which has over 10 million rows, so am trying to use PROC SQL or PROC EXPAND as opposed to data step processing.

Can someone provide some help?

Thanks!

- jkurka

Re: How to find a max value while restricting number of rows

Reeza — Fri, 12 Feb 2016 04:10:58 GMT

Is there some variable that identifies the 1800 rows?

Re: How to find a max value while restricting number of rows

jkurka — Fri, 12 Feb 2016 04:32:02 GMT

Using PROC SQL and the monotonic () function I created a 'rows' variable, but that is all besides the variable (column) that has the data I want to aggregate.

Re: How to find a max value while restricting number of rows

jkurka — Fri, 12 Feb 2016 04:39:09 GMT

I could provide an example for visualization. I'm sure that would help!

Say I'm starting with this and want to find the max within a range of each 3 variables:

rows var

1 0

2 2

3 4

4 1

5 4

6 2

7 0

8 5

9 1

I would like my output to show:

row var

1 4 --> max of first set of 3

4 4 --> max of next set of 3

7 5 --> max of next set 3

Re: How to find a max value while restricting number of rows

SASKiwi — Fri, 12 Feb 2016 06:57:17 GMT

It's simple in a DATA step:

data want;
  keep row maximum;
  set have;
  retain maximum 0;
  maximum = max(maximum, var);
  if mod(_n_, 1800) = 0 then do;
    row = _n_;
    output; 
    maximum = 0;
  end;
run;

Note though that the data is read in the order it is stored. If the stored order changes then so do the maximums.

Re: How to find a max value while restricting number of rows

data_null__ — Fri, 12 Feb 2016 09:37:51 GMT

PROC SUMMARY has features that make this easy. First you can use a view to add the group variable to your data, virtually speaking. The IDGROUP OUTPUT statement option finds the MAX of age for each BY G(roup) created in the VIEW and the OUT sub-option defines the variable(s) to write to the data set that correspond to the MAX(AGE). The OBS sub-option creates the _OBS_ variable with the observation number.

%let gsize=3;

data rowsv / view=rowsv;
   set sashelp.class;
   g + mod(_n_,&gsize) eq 1;
   run;
proc summary data=rowsv;
   by g;
   output out=max&gsize idgroup(max(age) obs out(name--weight)=);
   run;

Re: How to find a max value while restricting number of rows

jkurka — Fri, 12 Feb 2016 14:36:44 GMT

Won't this take a really long time to go through 10 million rows?

Re: How to find a max value while restricting number of rows

data_null__ — Fri, 12 Feb 2016 14:45:42 GMT

The actual time it takes depends on many factors. It should take about the same for a procedure to read the data as data step. Ideally you want to make one pass of the data.

Re: How to find a max value while restricting number of rows

jkurka — Fri, 12 Feb 2016 14:47:36 GMT

Thank you data_null__. Yes I'm keeping track of both posts. Wasn't aware of the duplicate until now. I'm giving your solution a try now!

Re: How to find a max value while restricting number of rows

data_null__ — Fri, 12 Feb 2016 14:54:01 GMT

It would be a good idea to show an example of your data. If you have date-time and you want to group accordingly it might be better to use the date-time and a format or other rounding technique instead of observations number.

Re: How to find a max value while restricting number of rows

jkurka — Fri, 12 Feb 2016 15:08:52 GMT

The data aren't timestamped. I've attached an excel example of a chuck of one file. As you can see it's simple, and just wanting the maximum value of every 1800 rows of the flt_z variable (last column).

I have the time that corresponds to the first row, but won't timestamp the full dataset due to it taking so long.