Max and Min value for numbers in order

[zanmar]

Hello,

I want to calculate the minimum and maximum value in cases of numbers in order (i+1) that belongs in the same column and in cases of one single number returns the same. I have thought that I need a counter that change when the next number not is i+1 (in this case of the example I expected Var3: 1112334566 (marked distinct the diferente groups 1-2-3, 8-9, 5-6 ...).

I have a table like this and I need a final table like the right one:

Var1 Var2                                     Var1     Min     Max

  1       1                                            1          1         3

  1       2                                            1          8         8

  1       3                                            2          9        10

  1       8                       --->>             2          25       25 

  2       9                                            2          33       33

  2      10                                           2          50       50

  2       25                                          3          5          6

  2       33

  2       50

  3        5

  3        6

Thanks a lot,

Maria

[RW9]

Sorry, am not following you, or you are missing some vital information.  Why with:

Var1 Var2                                     Var1     Min     Max

  1       1                                            1          1         3

  1       2                                            1          8         8

  1       3                                            2          9        10

  1       8                       --->>             2          25       25 

Is var1 min=1 and max=3 - this is not true, min=1 and max=8, that is the definition of a min/max within group, or do you have some other group information not provided in that example?

Also, post test data in the form of a datastep, e.g:

data have;
  input var1 var;
datalines;
1 1
1 2
1 3
;
run;

[zanmar]

Hi RW9,

Thank you for your answer!

I put here an example with numbers because my table is with dates but I can´t copy it here (I work in a protected server).

To be more detailed, there is a table with dates (Var2) by cities (Var1). I need to find the day before and after of these dates but in cases of periods, I need the day after and before of the period!

That is why I said that I look for the max and min of the numbers in order and not the max and min only by Var1. I would like to find the Min and Max by period (numbers in order) and Var1.

I hope it helps more!

Thanks a lot,

Maria

[RW9]

Without examples of your data I don;t see how I can help further.  There is no group information provided in the post you have given, so min/max does not make sense.  Either type some data or follow this post to create some test version of your data:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[zanmar]

You are right!

Here is the table I have:

data have;

input city date: date9.;

format date9.;

datalines;

0 01APR17

0 02APR17

0 03APR17

0 01MAY17

0 18MAY17

1 19MAY17

1 21MAY17

1 04JUN17

1 05JUN17

2 02FEB17

2 03FEB17

;

run;

There are periods and dates as well by each code of city.

I need a final table like this:

city    day_before      day_after

0         31MAR2017     04APR2017

0         30APR2017     02MAY2017

0         17APR2017     19MAY2017

1         18MAY2017     20MAY2017

1         20MAY2017     22MAY2017

1         03JUN2017     06JUN2017

2         01FEB2017     04FEB2017

It is more helpful now?

Thank you very much!

[RW9]

Yes, but your still missing conveying the "period" to us.  For instance let me show you:

data have;
  input city period date: date9.;
  format date date9.;
datalines;
0 1 01APR17
0 1 02APR17
0 1 03APR17
0 2 01MAY17
0 2 18MAY17
1 1 19MAY17
1 1 21MAY17
1 1 04JUN17
1 1 05JUN17
2 2 02FEB17
2 2 03FEB17
;
run;

data want;
  set have;
  by city period;
  retain date_before date_after;
  format date_before date_after date9.;
  if first.period then date_before=date-1;
  if last.period then do;
    date_after=date+1;
     output; 
  end;
run;

Basically I take date-1 on first occurence within the group city/period, and date + 1 on last occurence of city/period and only output last record, the retain keeps the first through.  But you have to know what the "group" is, city is fine in your example, but what is period, how is it defined?

[zanmar]

The second column you put like period in datalines is what I need to calculate firstly (my real table has 20.000 obs).

One period is when there are dates continuous (date+1) , for example here we have 1-3April, 19-21May, 4-5Juny and 2-3Feb.

[RW9 · Accepted solution]

Something like:

data have;
  input city date: date9.;
  format date date9.;
datalines;
0 01APR17
0 02APR17
0 03APR17
0 01MAY17
0 18MAY17
1 19MAY17
1 21MAY17
1 04JUN17
1 05JUN17
2 02FEB17
2 03FEB17
;
run;

data want;
  set have;
  by city;
  retain period lst_dt;
  if first.city then do;
    period=1;
    lst_dt=date;
  end;
  else do;
    if date ne lst_dt+1 then period=period+1;
    lst_dt=date;
  end;
run;

[zanmar]

It works!!

Thank you very much RW9 🙂 You are the best, fast and correct! 

Now the only think to do is to find the max and min but I can do it myself!!! 😉

Best regards,

Maria