BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zanmar
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1119 views
  • 2 likes
  • 2 in conversation