Condition where pour comparer si une date (format datetime20.) est egal +/5 a une autre datetime20.

[agdato]

Condition where pour comparer sisi une date (format datetime20.) d'une table A (want) se trouve dans une plage de +/- 5 jours par rapport à une date (format datetime20.) de la table B (want 2).

J'ai deux ensembles de données, want et want2, où je génère aléatoirement 20 dates au format datetime20., mais je n'arrive pas à créer la condition WHERE qui permet de sélectionner les dates communes, mon code ne seleccioné pas aucun date. 

Pourriez vous m'aider svp?

/* Crear tabla con 20 fechas adicionales */

data want;

    /* Definir la fecha inicial */

    chardate = "23/01/2023";

    numdate = input(chardate, ddmmyy10.);

    numdatetime = dhms(numdate, 0, 0, 0);

    format numdate date9. numdatetime datetime20.;

    output;

    /* Generar 20 fechas adicionales */

    do i = 1 to 10;

        /* Incrementar la fecha inicial para obtener fechas adicionales */

        numdate = intnx('day', numdate, 1); /* Incrementar en un día */

        numdatetime = dhms(numdate, 0, 0, 0);

        output;

    end;

run;

data want2;

    /* Definir la fecha inicial */

    chardate = "25/01/2023";

    numdate = input(chardate, ddmmyy10.);

    numdatetime2 = dhms(numdate, 0, 0, 0);

    format numdate date9. numdatetime2 datetime20.;

    output;

    /* Generar 20 fechas adicionales */

    do i = 1 to 10;

        /* Incrementar la fecha inicial para obtener fechas adicionales */

        numdate = intnx('day', numdate, 1); /* Incrementar en un día */

        numdatetime2 = dhms(numdate, 0, 0, 0);

prueba1=intnx("day", datepart(numdatetime2), 0);

prueba2=intnx("day", datepart(numdatetime2), -5);

prueba3=intnx("day", datepart(numdatetime2), 5);

        output;

    end;

run;

proc sql;

  create table fecha_matching as

  select 

    a.numdatetime as numdatetime,

    b.numdatetime2 as numdatetime2

  from want a, want2 b

  where

    intck('day', b.numdatetime2, a.numdatetime) <= 5

    and intck('day', b.numdatetime2, a.numdatetime) >= -5;

quit;

[agdato]

Je prefere si possible utiliser un left_join comme celle ci :


proc sql;

create table want3 as
select distinct
a.numdatetime

from want a
left join want2 b

on (a.numdatetime >= intnx("day",datepart(b.numdatetime2),-1)
and a.numdatetime <= intnx("day",datepart(b.numdatetime2),1))
/* where b.exe_soi_dtd between intnx('day', a.date_ex_bio, -5) and intnx('day', a.date_ex_bio, 5)*/
/* and case when a.date_decess is not null and b.BEN_DCD_DTE is not null
then a.date_decess = b.BEN_DCD_DTE
else 1
end

and INTCK('DTDAY', a.date_ex_bio, b.exe_soit_dtd) <= 5 /* +/- 5 días
a.finess_jur = b.ETA_NUM */


;
quit;

[Patrick · Accepted solution]

Below your code with some tweaks. Main issue was that in intck() you used 'day' instead of 'dtday' as date directive together with a SAS datetime value.

/* Crear tabla con 20 fechas adicionales */
data want;
  /* Definir la fecha inicial */
  chardate = "23/01/2023";
  format numdate date9. numdatetime datetime20.;
  /* Generar 20 fechas adicionales */
  do i = 0 to 10;
    /* Incrementar la fecha inicial para obtener fechas adicionales */
    numdate = intnx('day', input(chardate, ddmmyy10.), i); /* Incrementar en un día */
    numdatetime = dhms(numdate, 0, 0, 0);
    output;
  end;
run;

data want2;
  /* Definir la fecha inicial */
  chardate = "25/01/2023";
  format numdate date9. numdatetime2 datetime20.;

  /* Generar 20 fechas adicionales */
  do i = 0 to 10;
    /* Incrementar la fecha inicial para obtener fechas adicionales */
    numdate = intnx('day', input(chardate, ddmmyy10.), i); /* Incrementar en un día */
    numdatetime2 = dhms(numdate, 0, 0, 0);
    prueba1=intnx("day", datepart(numdatetime2), 0);
    prueba2=intnx("day", datepart(numdatetime2), -5);
    prueba3=intnx("day", datepart(numdatetime2), 5);
    output;
  end;
run;

proc sql;
/*  create table fecha_matching as*/
    select 
      a.i as a_i,
      a.numdatetime as numdatetime,
      b.i as b_i,
      b.numdatetime2 as numdatetime2,
      intck('dtday',a.numdatetime,b.numdatetime2) as diff
    from want a, want2 b
      where intck('dtday',a.numdatetime,b.numdatetime2) between -5 and 5
  ;
quit;

[agdato]

Merci Patrick ! c'est parfait !