Comparing variables in a table and eliminating odds

[Anita_n]

Dear all,

I have a table containing birthdays for a patients. Because this table is the combination of more tables, I have more columns with birthday. for example diagnosis bithday, therapie birthday, follow-up birthday and so on (up to about 8 birthdays)

I wish to compare these birthdays. If about let say seven birthdays are the same and only one is different then I will like to mark than column as maybe 1 and probably eliminate the odd in the next step

data test;
set test1;
if column1 ^= column2 and column2 ^= column3 and column3^= column4 
and column4 ^= column5 and column5 ^= column6 and column6 ^= column7 
and column7 ^= column8 then col_not_equal =1;
run;

using  a code like this is not elegant. Is there an elegant way to do this?

best regards

[Kurt_Bremser · Accepted solution]

First of all, your code will only work if NO two dates are equal, because you used AND instead of OR.

I would use an array over date columns 2 to n, and run a do loop over it and compare it to the first:

array dates {*} column2-column7;
flag = 0;
do i = 1 to dim(dates);
  if dates{i} ne column1 then flag = 1;
end;

[Anita_n]

@Kurt_Bremser, thanks for the code. I will try thank now and see

[Anita_n]

@Kurt_Bremser: is it also possible to compare data in rows with each other using this method
pat_id  fol_up_id  birthday
1         0  10/01/67
1       1    10/01/67

1      3     11/10/76
1    11    10/01/67

[Kurt_Bremser]

Similar method. Keep the first value in a retained variable, and compare to the others.

data want;
set have;
by pat_id;
retain _date flag;
if first.pat_id
then do;
  _date = date;
  flag = 0;
end;
else if date ne _date then flag = 1;
if last.pat_id; /* if you only want one observation per id */
drop _date;
run;

[Anita_n]

Thanks a lot for that

[PaigeMiller]

In your problem, does the year of the birthdays have to match all the other years, or do you want just the month-day (but not year) to match across all the birthdays?

[Anita_n]

actually all, day, month and year

[mkeintz]

This is a use case for MIN and MAX functions.

If all the birthdate vars are column1,column2, ... (i.e. the birthdates, and only the birthdates, all begin with "column"), then:

data test;
  set test1;
  if min(of column:)^=max(of column:) then flag=1;
run;

But if the varnames are not so neatly named, then:

data test;
  set test1;
  if min(bdatex, datebd, otherdate, ...) ^= max(bdatex, datebd, otherdate,...) then flag=1;
run;