Comparing date variables

[Kurt_Bremser]

And that's exactly what my second code delivers.

---

@Pandu2 wrote:
Please do read this, if this doesn't help, you can leave as-is without answering. Thankyou.

Like I said, I sincerely apologise for changing my mind but, after doing some testing I came up with another thought which suits the blueprint of the output dataset. Please do pick the rows which has first increase of amount. I'm again re-typing the things which I require from this below data.
01JAN2022 20APR2022 1411 69 115 1
01FEB2022 30JAN2022 1411 69 115 2
01FEB2022 30JAN2022 1411 69 115 3
03MAR2022 21FEB2022 1412 70 116 1
04MAR2022 29JAN2022 1412 70 116 2
1st rule:
The 1st appearance of Id, roll, num should be picked only after it meets the condition DATE1 >= DATE2. In this id, roll, num are primary keys and DATE1, DATE2, AMOUNT, PRODUCT has relation with Id,roll, num.
Explanation for 1st rule:
In the above given data, the 1st appearance of Id, roll, num doesn't meet the condition DATE1>= DATE2. This is where 2nd rules comes in :
If the 1st appearance of Id, roll, num (1411 69 115)doesn't meet the condition DATE1 >= DATE2, then when there's a increase in amount value compared to previous value then the first increase of amount row should be picked and that should also match DATE1 >= DATE2.
Continuing 1st rule : For the next id, roll, num i.e 1412 70 116 this should be picked cuz this is the 1st appearance of Id, roll, num and it meets the condition DATE1>= DATE2. But when I use your code it didn't pick the record which has date 03MAR2022 instead it picked both 03MAR2022 and 04MAR2022.
2nd rule explanation:
for the 1st id, roll, num (1411 69 115) for this it should pick the first increase in amount. I.e 01FEB2022 30JAN2022 1411 69 115 2 instead of 01FEB2022 30JAN2022 1411 69 115 3. I hope this makes more sense.

---

[Pandu2]

I've already done this by adding DATE1 in proc sort but when I didn't use that in the BY statement of your code. It got an error saying BY variables are not sorted properly.

[Kurt_Bremser]

You must add DATE1 at the END of the BY statement in PROC SORT, not as the first variable. The BY statement in my data step is then equal to the first three variables in the BY of the SORT, so you cannot get an "unsorted" ERROR.

[Pandu2]

Could you please help me on that. Thankyou

[Pandu2]

Yes, the roll , num are important as these three variable values are interlinked to other variable values. Based upon these 3 we get the data in my case. The variable id individually shouldn't give the data. In the end for some fields the id might be same but roll, num changes.
Example:
1411 69 115
1411 69 115
1411 70 116
1411 70 116
1411 71 117
1411 71 117
.
.
.

[Kurt_Bremser]

Depending on how groups are formed, this does what you ask for:

data want;
set have;
by id;
retain _flag _date1 _date2 _roll _num _amount;
if first.id
then do;
  _flag = 1;
  _amount = .;
  if date1 >= date2
  then do;
    _flag = 0;
    output;
  end;
end;
if amount > _amount
then do;
  _date1 = date1;
  _date2 = date2;
  _roll = roll;
  _num = num;
  _amount = amount;
end;
if last.id and _flag
then do;
  date1 = _date1;
  date2 = _date2;
  roll = _roll;
  num = _num;
  amount = _amount;
  output;
end;
drop _:;
run;

If this does not cover all your requirements, add additional rows to your example data and re-post the data step.

[Pandu2]

Sure, will check this out and let you know, thanks a bunch.

[Pandu2]

In the BY statement you used only id, does that work cuz Id remains same for fields but their adjacent roll, num changes. Could you please explain me this.