topic Re: Comparing variables in a table and eliminating odds in SAS Programming

Comparing variables in a table and eliminating odds

Anita_n — Thu, 11 Feb 2021 11:47:10 GMT

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

Re: Comparing variables in a table and eliminating odds

Kurt_Bremser — Thu, 11 Feb 2021 11:56:33 GMT

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;

Re: Comparing variables in a table and eliminating odds

PaigeMiller — Thu, 11 Feb 2021 11:58:38 GMT

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?

Re: Comparing variables in a table and eliminating odds

Anita_n — Thu, 11 Feb 2021 12:02:06 GMT

actually all, day, month and year

Re: Comparing variables in a table and eliminating odds

Anita_n — Thu, 11 Feb 2021 12:03:17 GMT

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

Re: Comparing variables in a table and eliminating odds

Anita_n — Thu, 11 Feb 2021 12:31:32 GMT

@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

Re: Comparing variables in a table and eliminating odds

mkeintz — Thu, 11 Feb 2021 12:32:05 GMT

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;

Re: Comparing variables in a table and eliminating odds

Kurt_Bremser — Thu, 11 Feb 2021 12:35:23 GMT

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;

Re: Comparing variables in a table and eliminating odds

Anita_n — Thu, 11 Feb 2021 12:42:04 GMT

Thanks a lot for that