topic Re: Check if column values are equal in SAS Programming

Check if column values are equal

CHGMartinez — Fri, 04 Jun 2021 20:00:55 GMT

Hi,

I have a dataset "Reproduction" with data from artificial inseminations(AI) in animals during 1 oestrous period.

Data Reproduction

ID AI1 AI2 AI3 AI4 AIn

1 B2 B2 B1 B2 4

2 B1 B1 B1 . 3

3 B1 B3 B3 B3 4

4 B2 B2 B2 . 3

5 B1 B1 . . 2

...(5400 obs)

The values "B1, B2 or B3" are male ID. I want to know if inseminations are with same male (event '1') or different male (event '0'), like this:

Data Reproduction

ID AI1 AI2 AI3 AI4 AIn Want

1 B2 B2 B1 B2 4 0

2 B1 B1 B1 . 3 1

3 B1 B3 B3 B3 4 0

4 B2 B2 B2 . 3 1

5 B1 B1 . . 2 1

...(5400 obs)

Re: Check if column values are equal

Tom — Fri, 04 Jun 2021 21:51:15 GMT

Just test if there is ever a value that does not match the first one.

data want;
  set have ;
  array ai ai1-ai4 ;
  do index=1 to ain until(want=0);
    want = ai[1] = ai[index] ;
  end;
  drop index;
run;

Re: Check if column values are equal

Ksharp — Sat, 05 Jun 2021 10:35:33 GMT

data have;
input ID      AI1  $   AI2  $  AI3 $   AI4  $ AIn;
cards;
1       B2      B2     B1     B2   4
2       B1      B1     B1     .       3
3       B1      B3     B3     B3   4
4       B2      B2     B2     .       3
5       B1      B1       .       .       2 
;

data want;
 if _n_=1 then do;
   length k $ 80;
   declare hash h();
   h.definekey('k');
   h.definedone();
 end;
set have;
array x{*} $ AI1-AI4;
do i=1 to dim(x);
  if not missing(x{i}) then do; k=x{i};h.ref();end;
end;
want=(h.num_items=1);
h.clear();
drop i k;
run;

Re: Check if column values are equal

CHGMartinez — Mon, 07 Jun 2021 14:07:09 GMT

Thanks, it help me a lot!!!
Ksharp,
You alse gave me a solution! thanks!

Re: Check if column values are equal

Ksharp — Tue, 08 Jun 2021 12:19:24 GMT

Tom,

What if the first variable is missing ?

data have;
input ID      AI1  $   AI2  $  AI3 $   AI4  $ AIn;
cards;
1       B2      B2     B1     B2   4
2       B1      B1     B1     .       3
3       B1      B3     B3     B3   4
4       B2      B2     B2     .       3
5       .      B1       .       .       2 
;

Re: Check if column values are equal

Tom — Tue, 08 Jun 2021 21:02:40 GMT

I doubt that is what they actually have, why have the counter variable if missing values are not at the end?

But it is not hard to adjust for missing values.

data want;
  set have ;
  array ai ai1-ai4 ;
  want=1;
  do index=1 to ain while(missing(ai[index])); 
  end;
  do index2=index+1 to dim(ai) until(want=0);
    if not missing(ai[index2]) then want = ai[index] = ai[index2] ;
  end;
  drop index index2;
run;

Re: Check if column values are equal

Ksharp — Wed, 09 Jun 2021 11:52:44 GMT

Tom,

What if all the variable were missing ?

data have;
input ID      AI1  $   AI2  $  AI3 $   AI4  $ AIn;
cards;
1       B2      B2     B1     B2   4
2       B1      B1     B1     .       3
3       B1      B3     B3     B3   4
4       B2      B2     B2     .       3
5       .      .       .       .       2 
;

Re: Check if column values are equal

Tom — Wed, 09 Jun 2021 12:47:33 GMT

What is the right answer for a subject with all missing values?

That case is not hard to test for.

array ai ai1-ai4 ;
if cmiss(of ai[*]) = dim(ai) then ....

Re: Check if column values are equal

Ksharp — Wed, 09 Jun 2021 13:39:26 GMT

Tom，

I think should be zero.

Re: Check if column values are equal

CHGMartinez — Wed, 09 Jun 2021 14:37:33 GMT

Interesting question... Thank you so much Tom and Ksharp

Actually missing values are just at the end.