topic How to group by different values of a variable and number them in sequence in SAS Programming

How to group by different values of a variable and number them in sequence

Chauncy — Thu, 01 Sep 2022 09:59:45 GMT

Now I have a simplified example.

As in the picture above, there are two variables. The variable y is the original data and variable x is the order variable I want.

You can see, y has a cretain regular pattern that most of its data follow 'cc bb aa cc bb aa..'(Notice that obs 11, for some reason, there's only one 'a'. And that's the only unsual pattern we considered.).

So, when I want a sequence for every two different y-values a number, it becomes a problem.

I try to use RETAIN statment, but as a fresh hand, I really need some support.

Thanks for your reading, and hope you can do me a favor.

Re: How to group by different values of a variable and number them in sequence

andreas_lds — Thu, 01 Sep 2022 10:29:37 GMT

Posting the code you have (using the little-running-man icon) would allow us to suggest code that fixes what you have, instead of posting something new.

x needs to be incremented each second time that the value of y changes. So, you need to retain: x, the last value of y and another variable, i call it c holding the number of changes.

data want;
   set have;
   retain x c _y;
   
   if _n_ = 1 then do;
      x = 1;
      c = 0;
   end;
   else do;
      if _y ne y then do;
         c = c + 1;
         
         if c = 2 then do;
            x = x + 1;
            c = 0;
         end;
      end;
   end;
   
   _y = y;
run;

Re: How to group by different values of a variable and number them in sequence

FreelanceReinh — Thu, 01 Sep 2022 11:25:04 GMT

Hello @Chauncy and welcome to the SAS Support Communities!

Once you have learned about BY-group processing, the sum statement and the MOD function, you will be able to implement the solution presented by andreas_lds in a more concise DATA step:

data want(drop=c);
set have;
by y notsorted;
if first.y then do;
  c+1;
  x+mod(c,2);
end;
run;

At the first observation of each group of equal y values ("if first.y ..."), the counter c is incremented ("c+1;"). If the value of c is odd (1, 3, 5, ...), variable x is incremented by 1 (=mod(c,2)) as well, otherwise its value doesn't change because the increment mod(c,2) is zero. For both c and x the initial values are 0 and they are retained by virtue of the sum statement. As variable c is not needed in dataset WANT, I have dropped it using the DROP= dataset option (but you may want to see it for better understanding).

Re: How to group by different values of a variable and number them in sequence

Chauncy — Fri, 02 Sep 2022 02:15:13 GMT

Thank you for your help! Your code really does what I needed. Comparing y and _y(previous y value) is a new idea for me. This gave me a deeper understanding of the underlying mechanism of data-step and retian statement.

Re: How to group by different values of a variable and number them in sequence

Chauncy — Fri, 02 Sep 2022 02:28:57 GMT

Thank you for your help ! Your code works, of course! The notsorted option is brand new for me, though I have learned BY statement. Looks like it could simplify many problems.