Try this.
My logic is this. if the rating increases in the last observation by group compared to the second last and the rating in the last obs is > 2. Then I assume that the rating has already been equal to 2 and has been increasing from then.
See if it works for you.
Data sample;
Input customer $ Rating month $;
Cards;
A 0 Jan19
A 1 Feb19
A 2 Mar19
A 2 Apr19
A 3 May19
A 4 Jun19
B 2 Apr19
B 3 May19
B 4 Jun19
B 5 Jul19
B 6 Aug19
B 5 Sep19
B 4 Oct19
C 2 Jan19
C 2 Feb19
C 3 Mar19
C 4 Apr19
C 5 May19
;
data want(drop = flag);
do _N_ = 1 by 1 until (last.customer);
set sample;
by customer;
flag = (dif(Rating) = 1 & Rating > 2);
end;
do _N_ = 1 to _N_;
set sample;
if flag then output;
end;
run;
Result:
customer Rating month A 0 Jan19 A 1 Feb19 A 2 Mar19 A 2 Apr19 A 3 May19 A 4 Jun19 C 2 Jan19 C 2 Feb19 C 3 Mar19 C 4 Apr19 C 5 May19
Can you show us a sample of your data please? makes it much easier to provide a usable code answer.
Please do not post your data as an image.
But.. what do you want the result to look like given this data?
@Shradha1 wrote:
Hi! Have uploaded a short table with the question for sample
Please post the data as data-step using datalines - so that we have something to work with. Also show the expected result using that dataset.
Try next code:
proc sort data=have; by customer month; run;
/* assumed month is a sas numeric date */
data want;
set have;
by customer;
retain max_rate out_flag;
if first.customer then do;
if rating ge 2 then out_flag=0; else flag=-1;
max_rate = rating;
end;
else do;
if flag ge 0 then and
rating ge max_rate then do;
out_flag=1;
max_rate=rating;
end; end;
if last.customer and out_flag ge 0;
run;
If you only want a single record with the customer id for the relevant customers, this should do it:
data want;
do until(last.customer);
set sample;
by customer;
dif=dif(rating);
if first.customer then
wanted=1;
else if dif<0 then
wanted=0;
end;
if wanted;
keep customer;
run;
If you want all the records for those customers, here's how:
data want;
do until(last.customer);
set sample;
by customer;
dif=dif(rating);
if first.customer then
wanted=1;
else if dif<0 then
wanted=0;
end;
do until(last.customer);
set sample;
by customer;
if wanted then
output;
end;
drop dif wanted;
run;
Try this.
My logic is this. if the rating increases in the last observation by group compared to the second last and the rating in the last obs is > 2. Then I assume that the rating has already been equal to 2 and has been increasing from then.
See if it works for you.
Data sample;
Input customer $ Rating month $;
Cards;
A 0 Jan19
A 1 Feb19
A 2 Mar19
A 2 Apr19
A 3 May19
A 4 Jun19
B 2 Apr19
B 3 May19
B 4 Jun19
B 5 Jul19
B 6 Aug19
B 5 Sep19
B 4 Oct19
C 2 Jan19
C 2 Feb19
C 3 Mar19
C 4 Apr19
C 5 May19
;
data want(drop = flag);
do _N_ = 1 by 1 until (last.customer);
set sample;
by customer;
flag = (dif(Rating) = 1 & Rating > 2);
end;
do _N_ = 1 to _N_;
set sample;
if flag then output;
end;
run;
Result:
customer Rating month A 0 Jan19 A 1 Feb19 A 2 Mar19 A 2 Apr19 A 3 May19 A 4 Jun19 C 2 Jan19 C 2 Feb19 C 3 Mar19 C 4 Apr19 C 5 May19
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!
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.