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

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

agdato — Fri, 05 Jan 2024 10:03:17 GMT

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;

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

agdato — Fri, 05 Jan 2024 10:17:01 GMT

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;

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

Patrick — Fri, 05 Jan 2024 11:35:00 GMT

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;

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

agdato — Fri, 05 Jan 2024 15:28:15 GMT

Merci Patrick ! c'est parfait !