Count change of values across variables in a row

Recker96 — Thu, 21 Oct 2021 15:10:15 GMT

Hi SAS wizards,

I have a list of customers and 24 month columns(mo1, mo2...) in front of each. Each can take values from 1 to n. However this 'n' is unique for each row only. For e.g. for customer A (say n=4), mo1=1, mo2=2, mo3=2, mo4=2, mo5=3, mo6=4 and so on til mo24. I want to count how many times this value changes across the 24 months for each customer (i.e. in this 6 month period in the example, the count would be 3). I understand the logic of doing it but don't how to code it in using macros/data step/loops etc in SAS.

The logic I have is:

Initiate a counter at 0 and a ref as mo1 value.
Check this ref vs mo2, mo3 etc sequentially and increase counter when value is different while also updating ref as the new value.
Return the Counter value at the end for each row/customer.

Re: Count change of values across variables in a row

PaigeMiller — Thu, 21 Oct 2021 15:30:19 GMT

Use an ARRAY in a DATA step

UNTESTED CODE

data want;
    set have;
    array mo mo1-mo24;
    count=0;
    do i=2 to 24;
         if mo(i)^=mo(i-1) then count=count+1;
    end;
    drop i;
run;

Re: Count change of values across variables in a row

Recker96 — Fri, 22 Oct 2021 04:13:33 GMT

This works exactly how I wanted it to. Thanks a lot PaigeMiller!

topic Re: Count change of values across variables in a row in SAS Programming

Count change of values across variables in a row

Re: Count change of values across variables in a row

Re: Count change of values across variables in a row