topic Re: Many dates comparison in SAS Data Management

Many dates comparison

sumsar — Mon, 18 Feb 2019 08:39:38 GMT

Hi Everyone,

I would like to create a macro which could compare all the dates of my database.
The idea is to create beforehand an unique dataset grouping all the dates of all the database in the chronological order.
For example, create a dataset with one line per subject grouping the date of birth, the date of consent, the date of follow-up and the date of end of study.
All my dates are in format DDMMYYYY10.
Subject_id Date_of_birth Date_of_consent Date_of_follow-up Date_of_end_of_study
1 xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx
2 xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx
3 xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx

My idea is to create a macro which would compare all the dates and detect all the inconsitencies in one instruction.
In this example, the macro would compare the date of birth to the date of consent, then to the date of follow-up, and then to the end of study.
If one of this date is < to the date of birth, create a colomn "querie" in which is specified "Inconsistencies detected between dates".
Then do the same thing with the date of consent, compare it to the date of follow-up and then to the date of end of study and if one of this date is < to date of consent, specify in the column querie "Inconsistencies detected between dates".
Is there someone who has already made this or who could help me?

Thank you very much.

Re: Many dates comparison

RW9 — Mon, 18 Feb 2019 09:25:29 GMT

There is no need (or in fact benefit) to create "a macro" or any other kind of fluff. You have data with certain key attributes (going to assume patient id and visit) and dates. So extract this information into one big dataset, proc sort nodupkey to get uniques, and then you can apply graphing, tabulating or program logic to look at this data.

data bigdatesdataset;
  set dm (keep=usubjid startdate rename=(dob=startdate) in=a)
        dm (keep=usubjid startdate rename=(enrol=startdate) in=b)
        ae (keep=usubjid startdate enddate in=c)
        ...;
  if a then visit="Date of Birth";
  if b then visit="Enrolment";
  if c then visit="AE";
  ...;
run;

You can get a nice long list of all the subjects/visits/dates - I use start/end so you can get period information or ranges - and then utilise that for checking.

Re: Many dates comparison

Kurt_Bremser — Mon, 18 Feb 2019 09:26:05 GMT

You don't need a macro for this, you can create the code dynamically from sashelp.vcolumn:

data _null_;
set sashelp.vcolumn end=eof;
where libname = 'WORK' and memname = 'HAVE' and type = 'num' and format = 'DDMMYY10.' and upcase(name) ne 'DATE_OF_BIRTH';
if _n_ = 1 then call execute('data want; set have; length querie $100;';
call execute('if date_of_birth > ' !! name !! ' then querie = "' !! trim(name) !! ' is less than date_of_birth!";');
if eof then call execute('run;');
run;

Replace WORK and HAVE with you real library and mebmer names.

Re: Many dates comparison

sumsar — Mon, 18 Feb 2019 09:32:53 GMT

Thank you for your answer.

However, it seems that you compare all the dates to date of birth only, or I would like to compare all the dates between each other following their chronological order, I don't know if I'm clear.

Re: Many dates comparison

Kurt_Bremser — Mon, 18 Feb 2019 09:44:41 GMT

Since you can't retrieve the "chronological order" from any resource, you have to hard-code the logic anyway. Trying to "automate" such a logic just leads to inflated and unmaintainable code.

Re: Many dates comparison

sumsar — Mon, 18 Feb 2019 09:51:07 GMT

Ok, I thougt it was possible to put all the dates in chronological order in a list and then to make a double do loop :

I = 1 to last column - 1

J=2 to last column

And then compare list[i] to list[j]

I used that logic before with R software but I don't know SAS very well.

Thank you.

Re: Many dates comparison

Kurt_Bremser — Mon, 18 Feb 2019 11:17:23 GMT

You can do that with an array, and you set the order in the array definition:

data want;
set have;
array dates{*} Date_of_birth Date_of_consent Date_of_follow-up Date_of_end_of_study;
length querie $100;
do i = 1 to dim(dates);
  do j = i to dim (dates);
    if dates{i} > dates{j}
    then do;
      querie = vname(date{i}) !! ' is larger than ' !! vname(dates{j}) !! '!';
      output;
    end;
  end;
end;
run;

Re: Many dates comparison

sumsar — Mon, 18 Feb 2019 12:48:28 GMT

Thank you, it works perfectly!