Fluorite | Level 6

## Check if column values are equal

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Check if column values are equal

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;``````
9 REPLIES 9
Super User

## Re: Check if column values are equal

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;``````
Super User

## Re: Check if column values are equal

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
;
```

Super User

## Re: Check if column values are equal

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;``````
Super User

## Re: Check if column values are equal

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
;```
Super User

## Re: Check if column values are equal

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 ....``````
Super User

## Re: Check if column values are equal

Tom，

I think should be zero.

Fluorite | Level 6

## Re: Check if column values are equal

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

Actually missing values are just at the end.
Super User

## Re: Check if column values are equal

``````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;``````
Fluorite | Level 6

## Re: Check if column values are equal

Thanks, it help me a lot!!!
Ksharp,
You alse gave me a solution! thanks!
Discussion stats
• 9 replies
• 3214 views
• 7 likes
• 3 in conversation