Soins infirmiers libéraux par acte et par ALD - Conversion SAS vers ORACLE pour jointures homogènes et SAS asynchrone

Bonjour à toutes et tous,

Etant nouvel utilisateur du SNDS, je vous sollicite à propos de plusieurs problèmes rencontrés pour réaliser une extraction des soins infirmiers par département, par ALD patient et par type d’acte sur le champ France entière pour l’année 2023.

(1) Je récupère à partir de la magic loop les soins dispensés par les infirmiers libéraux par département et par acte sur une année à partir de la table des prestations (ER_PRS_F).
(2) Conformément aux bonnes pratiques mentionnées en formation Données individuelles bénéficiaires (DIB), je convertis cette table SAS vers l’espace ORACLE (table ORAUSER.BENEF_2023) à l’aide de la commande BULKLOAD.
(3) J’opère ensuite une jointure avec le référentiel bénéficiaire médicalisé (IR_IMB_R) pour récupérer les ALD actives correspondantes aux soins.
(4) Idem avec les référentiels de prestations et de pathologies ALD pour récupérer les libellés.
(5) J’extrais enfin les montants de dépenses par département/type d’acte/ALD.

Malgré des tests concluants en sas synchrone et asynchrone en limitant l’extraction à un département (BEN_RES_DPT = '027 dans le where du proc sql magic_loop), je rencontre plusieurs problèmes en passant au champ France entière :

  • La conversion vers Oracle de la table issue de la magic_loop est très longue dans les deux cas (presque autant de runtime que celui de la magic_loop !). Auriez-vous un conseil pour optimiser ce transfert (il y a près de 2 Md d’observations) ?
  • Lors du passage en France entière, le traitement renvoie une erreur en utilisant le sas asynchrone.

Pourriez-vous s’il vous plaît m’aider à résoudre ces différents problèmes ?

Je vous remercie par avance pour votre aide.

Bien à vous,
Clément

options LOCALE=FRENCH ;
option mprint symbolgen;

/* On lance la magic_loop pour obtenir les soins infirmiers en 2023 par type de prestations et par département */


/** Etape 1 : On fait sa requête  **/
/*Extraction des Données sur 1 Mois de Flux avec sa propre requête */

/**Etape 2 : MACRO MA_REQUETE***/
/** On remplace et modifie sa propre  requête selon les 3 consignes suivantes : */
/* 1- la Table ER_PRS_F Avec l'Alias T1 */
/* 2- LA TABLE générée par la requete est WORK.CONSO */ 
/* 3-  On conserve les 2 lignes indiquées pour les conditions sur les dates de soins et de Flux **/
%MACRO MA_REQUETE;

/**On intègre Ci-dessous SA REQUETE (PROC SQL) en tenant compte des consignes précédentes **/
PROC SQL;
   CREATE TABLE WORK.CONSO AS 
   SELECT t1.BEN_RES_DPT, 
          t1.BEN_NIR_PSA, 
          t1.BEN_RNG_GEM, 
          t1.BSE_PRS_NAT, 
          t1.PRS_PAI_MNT, 
          t1.EXE_SOI_DTD, 
          t1.EXE_SOI_DTF, 
          t1.FLX_DIS_DTD
      FROM ORAVUE.ER_PRS_F t1
      WHERE t1.DPN_QLF NOT = 71 AND t1.PSE_ACT_NAT IN 
           (
           24,
           86
           ) AND t1.FLX_DIS_DTD = &DFLUX AND t1.EXE_SOI_DTD BETWEEN &DEBSOIN AND 
           &FINSOIN;
QUIT;

%MEND MA_REQUETE;


/**Etape 3 : PARAMETRES EN ENTREE***/

%LET BORNE = 1; /* 1 : Oui 0 : Non.  Indique si on a une Date de fin soins en paramètre de la requete. Si 0 La date de fin de soins sera celle du dernier  mois dispo**/

%LET DEBUT = 20230101; /*Date de Début de Soins sous la forme AAAAMMJJ **/

%LET FIN = 20231231; /*Date de Fin de Soins sous la forme AAAAMMJJ. Modification du Paramètre non nécessaire si BORNE=0 **/

%LET NBFLUX = 6; /* Indique le Nombre de mois de Flux prix en compte après Date de Fin de Soins  Modification du Paramètre non nécessaire si BORNE=0 **/

/**FIN DES PARAMETRES **/

/** MACRO MAGIC_LOOP **/
/** ...Et On appelle  MAGIC_LOOP **/

%m_magic_loop;


/* (1) Conversion de la sortie magic_loop vers l'espace ORAUSER pour faire une jointure homogène*/

/* Création de la table temporaire orauser.BENEF_2023 */
proc sql;
   drop table orauser.BENEF_2023;
   create table orauser.BENEF_2023 (BULKLOAD=yes BL_DATAFILE = "%sysfunc(pathname(work))/ttt.xyz" BL_DELETE_DATAFILE = yes) as
   select * from WORK.COMPIL_CONSO;
   drop table WORK.COMPIL_CONSO;
   drop table WORK.VERIF_MAGIC_LOOP;
quit;

/*calcul des stats de la table personnelle*/
%m_stats_table(nom_table=orauser.BENEF_2023);

/* (2) Jointure avec le référentiel bénéficiaire médicalisé
   (3) Jointures avec les référentiels de prestations et de pathologies ALD
   (4) Extraction des montants de dépense par département/type d'acte/ALD */



/* Création de la table SASDATA1.DEP_INF_ACTE_ALD */
proc sql;
   create table SASDATA1.DEP_INF_ACTE_ALD as 
      select t6.BEN_RES_DPT, 
             t4.ALD_030_COD, 
             t5.ALD_030_LIB, 
             t4.CIM_LIB, 
             t6.BSE_PRS_NAT, 
             t7.PRS_NAT_LIB, 
             /* DEP_TOT */
             sum(t6.PRS_PAI_MNT) format=13.2 as DEP_TOT
      from ORAVAL.IR_CIM_V t4
      inner join ORAVAL.IR_ALD_V t5 
         on t4.ALD_030_COD = t5.ALD_030_COD
      inner join (
         select t3.BEN_RES_DPT, 
                t2.BEN_NIR_PSA, 
                t2.BEN_RNG_GEM, 
                t2.IMB_ALD_NUM, 
                t2.MED_MTF_COD, 
                t2.INS_DTE, 
                t2.IMB_ALD_DTD, 
                t2.IMB_ALD_DTF, 
                t3.PRS_PAI_MNT, 
                t3.BSE_PRS_NAT, 
                /* LastMaj */
                max(t2.INS_DTE) as LastMaj
         from ORAVUE.IR_IMB_R t2
         inner join orauser.BENEF_2023 t3 
            on t2.BEN_NIR_PSA = t3.BEN_NIR_PSA 
            and t2.BEN_RNG_GEM = t3.BEN_RNG_GEM
         where t2.IMB_ALD_DTD <= '31Dec2023:0:0:0'dt 
           and (t2.IMB_ALD_DTF = '1Jan1600:0:0:0'dt 
                or t2.IMB_ALD_DTF >= '1Jan2023:0:0:0'dt) 
           and t2.IMB_ETM_NAT in (41, 43, 45)
         group by t3.BEN_RES_DPT, 
                  t2.BEN_NIR_PSA, 
                  t2.BEN_RNG_GEM, 
                  t2.IMB_ALD_NUM, 
                  t2.MED_MTF_COD, 
                  t2.INS_DTE, 
                  t2.IMB_ALD_DTD, 
                  t2.IMB_ALD_DTF, 
                  t3.PRS_PAI_MNT, 
                  t3.BSE_PRS_NAT
         having t2.INS_DTE = max(t2.INS_DTE)
      ) t6 
         on trim(t4.CIM_COD) = trim(t6.MED_MTF_COD)
      inner join ORAVAL.IR_NAT_V t7 
         on t7.PRS_NAT = t6.BSE_PRS_NAT
      group by t6.BEN_RES_DPT, 
               t4.ALD_030_COD, 
               t5.ALD_030_LIB, 
               t4.CIM_LIB, 
               t6.BSE_PRS_NAT, 
               t7.PRS_NAT_LIB
      order by t4.ALD_030_COD;
	  drop table ORAUSER.BENEF_2023;
quit;

EDIT : j’ai concaténé en une seule requête pour éviter le passage de SAS à Oracle.

options LOCALE=FRENCH ;
option mprint symbolgen;

/* On lance la magic_loop pour obtenir les soins infirmiers en 2023 par type de prestations et par département */

%MACRO MA_REQUETE;

/**On intègre Ci-dessous SA REQUETE (PROC SQL) en tenant compte des consignes précédentes **/
proc sql;
   /* Création de la table finale WORK.CONSO */
   create table WORK.CONSO as 
      select t6.BEN_RES_DPT, 
             t4.ALD_030_COD, 
             t5.ALD_030_LIB, 
             t4.CIM_LIB, 
             t6.BSE_PRS_NAT, 
             t7.PRS_NAT_LIB, 
             /* DEP_TOT */
             sum(t6.PRS_PAI_MNT) format=13.2 as DEP_TOT
      from ORAVAL.IR_CIM_V t4
      inner join ORAVAL.IR_ALD_V t5 
         on t4.ALD_030_COD = t5.ALD_030_COD
      inner join (
         /* Sous-requête pour extraire et filtrer les données avec le référentiel bénéficiaires */
         select t3.BEN_RES_DPT, 
                t2.BEN_NIR_PSA, 
                t2.BEN_RNG_GEM, 
                t2.IMB_ALD_NUM, 
                t2.MED_MTF_COD, 
                t2.INS_DTE, 
                t2.IMB_ALD_DTD, 
                t2.IMB_ALD_DTF, 
                t3.PRS_PAI_MNT, 
                t3.BSE_PRS_NAT, 
                /* LastMaj */
                max(t2.INS_DTE) as LastMaj
         from ORAVUE.IR_IMB_R t2
         inner join (
            /* Sous-requête pour sélectionner les données de ORAVUE.ER_PRS_F avec conditions */
            select BEN_RES_DPT, 
                   BEN_NIR_PSA, 
                   BEN_RNG_GEM, 
                   BSE_PRS_NAT, 
                   PRS_PAI_MNT, 
                   EXE_SOI_DTD, 
                   EXE_SOI_DTF, 
                   FLX_DIS_DTD
            from ORAVUE.ER_PRS_F
            where DPN_QLF NOT = 71 
              and PSE_ACT_NAT in (24, 86)
              and FLX_DIS_DTD = &DFLUX 
              and EXE_SOI_DTD between &DEBSOIN and &FINSOIN
         ) t3 
            on t2.BEN_NIR_PSA = t3.BEN_NIR_PSA 
            and t2.BEN_RNG_GEM = t3.BEN_RNG_GEM
         where t2.IMB_ALD_DTD <= '31Dec2023:0:0:0'dt 
           and (t2.IMB_ALD_DTF = '1Jan1600:0:0:0'dt 
                or t2.IMB_ALD_DTF >= '1Jan2023:0:0:0'dt) 
           and t2.IMB_ETM_NAT in (41, 43, 45)
         group by t3.BEN_RES_DPT, 
                  t2.BEN_NIR_PSA, 
                  t2.BEN_RNG_GEM, 
                  t2.IMB_ALD_NUM, 
                  t2.MED_MTF_COD, 
                  t2.INS_DTE, 
                  t2.IMB_ALD_DTD, 
                  t2.IMB_ALD_DTF, 
                  t3.PRS_PAI_MNT, 
                  t3.BSE_PRS_NAT
         having t2.INS_DTE = max(t2.INS_DTE)
      ) t6 
        /* Jointure avec les référentiels ALD et prestations pour ajouter les libellés */
         on trim(t4.CIM_COD) = trim(t6.MED_MTF_COD)
      inner join ORAVAL.IR_NAT_V t7 
         on t7.PRS_NAT = t6.BSE_PRS_NAT
      group by t6.BEN_RES_DPT, 
               t4.ALD_030_COD, 
               t5.ALD_030_LIB, 
               t4.CIM_LIB, 
               t6.BSE_PRS_NAT, 
               t7.PRS_NAT_LIB
      order by t4.ALD_030_COD;
quit;
%MEND MA_REQUETE;


/**Etape 3 : PARAMETRES EN ENTREE***/

%LET BORNE = 1; /* 1 : Oui 0 : Non.  Indique si on a une Date de fin soins en paramètre de la requete. Si 0 La date de fin de soins sera celle du dernier  mois dispo**/

%LET DEBUT = 20230101; /*Date de Début de Soins sous la forme AAAAMMJJ **/

%LET FIN = 20231231; /*Date de Fin de Soins sous la forme AAAAMMJJ. Modification du Paramètre non nécessaire si BORNE=0 **/

%LET NBFLUX = 6; /* Indique le Nombre de mois de Flux prix en compte après Date de Fin de Soins  Modification du Paramètre non nécessaire si BORNE=0 **/

/**FIN DES PARAMETRES **/

/** MACRO MAGIC_LOOP **/
/** ...Et On appelle  MAGIC_LOOP **/

%m_magic_loop;

Bonjour,

Merci pour votre question.
Nous allions effectivement vous proposer de faire la requête (ci-dessous) en évitant le passage WORK-ORACLE pour l’extraction des ER_PRS_F.

En général, il est conseillé de mutualiser au maximum les requêtes afin d’extraire, dans la mesure du possible, seulement les prestations qui vont intéressent. Par exemple ici, une méthode peut être d’extraire d’abord la population d’étude avant d’extraire les lignes de la table prestations. De plus, si vous êtes seulement intéressés par la somme de la colonne PRS_PAI_MNT, de nombreuses lignes peuvent être égales à zéro, elles pourraient êtres exclues de l’extraction.

Voici le code associé :

/* ON RECUPERE LES BENEF EN ALD EN 2023 AVEC LIBELLES CIM10 ET ALD */
proc sql;
	create table ORAUSER.BENEF_ALD_2023 as
	select 
		t1.BEN_NIR_PSA, 
		t1.BEN_RNG_GEM, 
		t1.IMB_ALD_NUM, 
		t1.MED_MTF_COD, 
		t1.INS_DTE, 
		t1.IMB_ALD_DTD, 
		t1.IMB_ALD_DTF,
		/* valeurs ald */
		t2.ALD_030_LIB, 
		/* valeurs cim */
		t3.CAT_CIM_LIB
	from ORAVUE.IR_IMB_R t1
		inner join ORAVAL.IR_ALD_V t2 
		on t1.IMB_ALD_NUM = t2.ALD_030_COD
		inner join ORAVAL.IR_CCI_V t3
		on substr(t1.MED_MTF_COD,1,3) = substr(t3.CAT_CIM_COD,1,3)
	where /* en ald et ald active */
		t1.IMB_ALD_DTD <= '31Dec2023:0:0:0'dt 
		and (t1.IMB_ALD_DTF = '1Jan1600:0:0:0'dt or t1.IMB_ALD_DTF >= '1Jan2023:0:0:0'dt) 
		and t1.IMB_ETM_NAT in (41, 43, 45)
	group by t1.BEN_NIR_PSA, t1.BEN_RNG_GEM
	having t1.INS_DTE = max(t1.INS_DTE);
quit;

/* ON ECRIT SA REQUETE DANS LA MACRO */
%MACRO MA_REQUETE;
proc sql;
	create table WORK.CONSO as
	select 
		/* var table prs */
		t1.BEN_RES_DPT, 
		t1.BEN_NIR_PSA, 
		t1.BEN_RNG_GEM, 
		t1.BSE_PRS_NAT,
		t2.PRS_NAT_LIB, 
		t1.PRS_PAI_MNT, 
		t1.EXE_SOI_DTD, 
		t1.EXE_SOI_DTF, 
		t1.FLX_DIS_DTD
	from ORAVUE.ER_PRS_F t1
		inner join ORAUSER.BENEF_ALD_2023 t2
		on (t2.BEN_NIR_PSA = t1.BEN_NIR_PSA) and (t2.BEN_RNG_GEM = t1.BEN_RNG_GEM)
		inner join ORAVAL.IR_NAT_V t2 
		on t2.PRS_NAT = t1.BSE_PRS_NAT	
	where t1.DPN_QLF NOT = 71
		/* prs infirmiers */
		and t1.PSE_ACT_NAT IN (24,86) 
		/* période prs */
		and t1.FLX_DIS_DTD = &DFLUX. 
		and t1.EXE_SOI_DTD BETWEEN &DEBSOIN. AND &FINSOIN.;
quit;
%MEND MA_REQUETE;

/* PARAMETRES EN ENTREE */
%LET BORNE = 1; /* 1 : Oui 0 : Non.  Indique si on a une Date de fin soins en paramètre de 
	la requete. Si 0 La date de fin de soins sera celle du dernier mois dispo**/
%LET DEBUT = 20230101; /*Date de Début de Soins sous la forme AAAAMMJJ **/
%LET FIN = 20231231; /*Date de Fin de Soins sous la forme AAAAMMJJ. Modification du Paramètre 
	non nécessaire si BORNE=0 **/
%LET NBFLUX = 6; /* Indique le Nombre de mois de Flux prix en compte après Date de Fin de Soins  
	Modification du Paramètre non nécessaire si BORNE=0 **/

/* MACRO MAGIC_LOOP */
%m_magic_loop;

J’en profite pour ajouter que cette requête, comme la vôtre, extrait l’ensemble des prestations exécutées par des infirmiers libéraux en 2023 pour les personnes avec une ALD active en 2023. Cependant, les prestations exécutées ne sont pas forcément liées aux ALD actives. Ainsi, les remboursements des prestations peuvent être pris ou ne pas être pris en charge dans le cadre de l’ALD. Pour extraire les prestations prises en charge dans le cadre d’une ALD, il faut utiliser la variable MTF_COD de la table ER_PRS_F. Et si la personne a plusieurs ALD, vous ne pourrez pas savoir pour quelle ALD la prestation a été remboursée.

En vous souhaitant une bonne semaine,
Cordialement

Angélique Delbarre - HDH