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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.