topic Re: Max and Min value for numbers in order in SAS Programming

Max and Min value for numbers in order

zanmar — Wed, 19 Apr 2017 08:17:33 GMT

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

Re: Max and Min value for numbers in order

RW9 — Wed, 19 Apr 2017 08:24:52 GMT

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;

Re: Max and Min value for numbers in order

zanmar — Wed, 19 Apr 2017 08:42:34 GMT

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

Re: Max and Min value for numbers in order

RW9 — Wed, 19 Apr 2017 08:45:49 GMT

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-data-AKA-generate/ta-p/258712

Re: Max and Min value for numbers in order

zanmar — Wed, 19 Apr 2017 09:26:53 GMT

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!

Re: Max and Min value for numbers in order

RW9 — Wed, 19 Apr 2017 09:38:47 GMT

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?

Re: Max and Min value for numbers in order

zanmar — Wed, 19 Apr 2017 09:51:48 GMT

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.

Re: Max and Min value for numbers in order

RW9 — Wed, 19 Apr 2017 10:17:46 GMT

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;

Re: Max and Min value for numbers in order

zanmar — Wed, 19 Apr 2017 10:45:12 GMT

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