topic Re: How to select those observations for which the value of a variable keeps increasing and never lo in SAS Programming

How to select those observations for which the value of a variable keeps increasing and never lowers

Shradha1 — Thu, 07 Jan 2021 07:31:06 GMT

I have a list of customers and their monthly ratings ranging from 0 to 9 for all the months. I want to select only those customers for whom once the rating reached value 2 or above, it kept on increasing thereafter and never fell back to a lower rating(0 or 1), or stayed the same. In other words, I want those customers for whom their rating has been 2 or above, with monotonously increasing in the later months. I am using Sas 9.4.

Data sample;
Input customer$8. Rating month date9.;
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 May 19
;
Run;

As per requirement, I will want the result to contain customer A and Customer C

Re: How to select those observations for which the value of a variable keeps increasing and never lo

PeterClemmensen — Thu, 07 Jan 2021 06:28:31 GMT

Can you show us a sample of your data please? makes it much easier to provide a usable code answer.

Re: How to select those observations for which the value of a variable keeps increasing and never lo

Shradha1 — Thu, 07 Jan 2021 06:56:38 GMT

Hi! Have uploaded a short table with the question for sample

Re: How to select those observations for which the value of a variable keeps increasing and never lo

PeterClemmensen — Thu, 07 Jan 2021 07:05:28 GMT

Please do not post your data as an image.

But.. what do you want the result to look like given this data?

Re: How to select those observations for which the value of a variable keeps increasing and never lo

andreas_lds — Thu, 07 Jan 2021 07:06:54 GMT

@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.

Re: How to select those observations for which the value of a variable keeps increasing and never lo

Shmuel — Thu, 07 Jan 2021 07:24:31 GMT

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;

Re: How to select those observations for which the value of a variable keeps increasing and never lo

Shradha1 — Thu, 07 Jan 2021 07:32:04 GMT

Have updated the question.

Re: How to select those observations for which the value of a variable keeps increasing and never lo

Shradha1 — Thu, 07 Jan 2021 07:32:22 GMT

Have updated the post

Re: How to select those observations for which the value of a variable keeps increasing and never lo

s_lassen — Thu, 07 Jan 2021 08:33:39 GMT

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;

Re: How to select those observations for which the value of a variable keeps increasing and never lo

PeterClemmensen — Thu, 07 Jan 2021 08:34:38 GMT

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

Re: How to select those observations for which the value of a variable keeps increasing and never lo

Shradha1 — Thu, 07 Jan 2021 09:25:31 GMT

This was exactly what I was looking for. Thanks a lot!

Re: How to select those observations for which the value of a variable keeps increasing and never lo

Shradha1 — Thu, 07 Jan 2021 10:16:50 GMT

Just noticed that this code is missing cases like this:-
customer Rating month
A 0 Jan19
A 1 Feb19
A 2 Mar19
A 3 Apr19
A 3 May19
A 3 Jun19

In this case customer A is being left out while using this code whereas it is wanted.