topic Re: Arrays - comparing consecutive values in SAS Programming

Arrays - comparing consecutive values

zishaq — Wed, 27 May 2020 18:32:05 GMT

Hi,

I have a dataset below and want to compute the columns shown in red:

Individual Month_01 Month_02 Month_03 Month_04 INSTANCES_EQUAL NUMBER_POSITIVE RATIO TYPE

X . 100 100 . 1 2 0.5 FIXED

Y 300 300 300 300 3 4 0.75 FIXED

Z 450 500 450 500 0 4 0 VARIABLE

INSTANCES_EQUAL compares the exact value in each consecutive month in pairs (i.e. month_01 against month_02, month_02 against month_03 etc). I also want to allow a leeway of +-5 either way. So taking individual X, if they had a value between 95 and 105 in Month_03 then this is fine and we can say that this is an instance. Also, it's important that it only compares values that are non-missing (i.e. greater than zero).

NUMBER_POSITIVE provides the number of positive values (i.e. greater than zero) for each row.

RATIO is a calculation where INSTANCES_EQUAL / NUMBER_POSITIVE.

TYPE is a character variable which takes ratio value and categorises this as "FIXED" if >=0.5 and "VARIABLE" if < 0.5

I feel like there is a shortcut way of doing this in one program node using ARRAYS but not sure where to start?!

Would be grateful for any advice.

Thanks

Re: Arrays - comparing consecutive values

PeterClemmensen — Wed, 27 May 2020 18:53:26 GMT

Try this

data have;
input Individual $ Month_01-Month_04;
datalines;
X .   100 100 .
Y 300 300 300 300
Z 450 500 450 500
;

data want(drop=i);
   set have;
   array m{*} Month_01-Month_04;
   instances_equal = 0;
   do i=1 to dim(m)-1;
      if m[i+1] = m[i] then instances_equal+1;
   end;
   number_positive = n(of m[*]);
   ratio = instances_equal / number_positive;
   type = ifc(ratio ge .5, "FIXED", "VARIABLE");
run;

Re: Arrays - comparing consecutive values

zishaq — Wed, 27 May 2020 19:19:51 GMT

Thanks!

How do I amend the do loop so we accept instances where the values are within +-5 when comparing? In other words it doesn't have to be an exact match but has to be close...

Re: Arrays - comparing consecutive values

PeterClemmensen — Wed, 27 May 2020 19:54:42 GMT

How about

data have;
input Individual $ Month_01-Month_04;
datalines;
X .   100 100 .
Y 300 300 300 300
Z 450 500 450 500
;

data want(drop=i);
   set have;
   array m{*} Month_01-Month_04;
   instances_equal = 0;
   do i=1 to dim(m)-1;
      if . < abs(m[i+1]-m[i]) <= 5 then instances_equal+1;
   end;
   number_positive = n(of m[*]);
   ratio = instances_equal / number_positive;
   type = ifc(ratio ge .5, "FIXED", "VARIABLE");
run;

Re: Arrays - comparing consecutive values

Tom — Wed, 27 May 2020 19:58:04 GMT

@zishaq wrote:

Thanks!

How do I amend the do loop so we accept instances where the values are within +-5 when comparing? In other words it doesn't have to be an exact match but has to be close...

So you mean instead of testing

x = y

You want to test

-5 <= (x-y) <= 5