如何在SAS EG中高效获取数据表的最新数据

问题背景 #

我有一张不定期更新的数据表，需要始终从中获取最新数据，可通过列PERIODAG_D（时间戳变量）是否接近当前日期判断数据是否为最新。

当前繁琐的实现方式 #

我当前通过outobs=1仅获取一条观测，并按PER_DAG_I（数值型日期变量）降序排序来拿到最新日期，再存入宏变量用于后续筛选：

步骤1：获取最新日期记录 #

PROC SQL OUTOBS=1;
   CREATE TABLE DESC_SORT AS 
   SELECT DISTINCT t3.PER_DAG_I, 
          t3.PERIODAG_D
      FROM COREPLNZ.KXYZ1000FCT t1
           LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t2 ON (t1.SKEMA_XYZ_JUNK_I = t2.SKEMA_XYZ_JUNK_I)
           LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t4 ON (t1.KILDEFACT_I = t4.KILDEFACT_I)
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t3 ON (t1.OPGOR_DAG_I = t3.PER_DAG_I)
      WHERE t4.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' AND t2.SKEMA_KODE = 'C 73.00'
      ORDER BY t3.PER_DAG_I DESC;
QUIT;

步骤2：将日期转换为宏变量 #

PROC SQL;
   CREATE TABLE DESC_SORT_FORMAT AS 
   SELECT t1.PERIODAG_D as str_perdag_Desc_sort, 
          t1.PERIODAG_D AS str_timestamp_Desc_sort,
          ("'"!!put(t1.PERIODAG_D,datetime22.3)!!"'dt") as str_SAStimestamp_Desc_sort
      FROM DESC_SORT t1;
QUIT;

PROC SQL NOPRINT;
SELECT DISTINCT 
          str_perdag_Desc_sort,
          str_timestamp_Desc_sort,
          str_SAStimestamp_Desc_sort
INTO      :str_perdag_Desc_sort,
          :str_timestamp_Desc_sort,
          :str_SAStimestamp_Desc_sort
FROM DESC_SORT_FORMAT;

步骤3：使用宏变量筛选最新数据 #

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_TXYZ1000FCT_000F(label="WORK.QUERY_FOR_TXYZ1000FCT_000F") AS 
   SELECT t2.PERIODAG_D AS OpgoerelseDato, 
          t6.PERIODAG_D AS AfviklingDato, 
          t1.KILDEFACT_Key_I, 
          t3.X_ORDINATE, 
          t3.Y_ORDINATE, 
          t3.Z_ORDINATE, 
          t4.SKEMA_KODE, 
          t4.SKEMA_NAVN, 
          t5.KILDEFACT_NAVN, 
          t7.KUNDE_SCD_I, 
          t7.KONTO_SCD_I, 
          t2.PER_DAG_I
      FROM COREPLNZ.KXYZ1000FCT t1
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t2 ON (t1.OPGOR_DAG_I = t2.PER_DAG_I)
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t6 ON (t1.AFVIKL_DAG_I = t6.PER_DAG_I)
           LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t3 ON (t1.SKEMA_XYZ_JUNK_I = t3.SKEMA_XYZ_JUNK_I)
           LEFT JOIN COREPLNZ.TXYZ0100_SKEMA_DIM t4 ON (t1.SKEMA_I = t4.SKEMA_I)
           LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t5 ON (t1.KILDEFACT_I = t5.KILDEFACT_I)
           LEFT JOIN COREPLNZ.KLIK6000_RESTLOEBETID_FCT t7 ON (t1.KILDEFACT_Key_I = t7.RESTLOEBETID_FCT_I)
      WHERE t5.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' AND t4.SKEMA_KODE = 'C 73.00' AND t2.PERIODAG_D = 
           &str_SAStimestamp_Desc_sort
      ORDER BY t2.PER_DAG_I DESC;
QUIT;

这种方法步骤繁琐、效率低，以下是几种优化思路：

优化方案 #

1. 直接在SQL子查询中获取最新日期，无需中间表和宏变量 #

直接在主查询的WHERE子句中嵌入子查询，获取符合条件的最大PERIODAG_D，一步完成筛选：

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_TXYZ1000FCT_000F(label="WORK.QUERY_FOR_TXYZ1000FCT_000F") AS 
   SELECT t2.PERIODAG_D AS OpgoerelseDato, 
          t6.PERIODAG_D AS AfviklingDato, 
          t1.KILDEFACT_Key_I, 
          t3.X_ORDINATE, 
          t3.Y_ORDINATE, 
          t3.Z_ORDINATE, 
          t4.SKEMA_KODE, 
          t4.SKEMA_NAVN, 
          t5.KILDEFACT_NAVN, 
          t7.KUNDE_SCD_I, 
          t7.KONTO_SCD_I, 
          t2.PER_DAG_I
      FROM COREPLNZ.KXYZ1000FCT t1
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t2 ON (t1.OPGOR_DAG_I = t2.PER_DAG_I)
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t6 ON (t1.AFVIKL_DAG_I = t6.PER_DAG_I)
           LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t3 ON (t1.SKEMA_XYZ_JUNK_I = t3.SKEMA_XYZ_JUNK_I)
           LEFT JOIN COREPLNZ.TXYZ0100_SKEMA_DIM t4 ON (t1.SKEMA_I = t4.SKEMA_I)
           LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t5 ON (t1.KILDEFACT_I = t5.KILDEFACT_I)
           LEFT JOIN COREPLNZ.KLIK6000_RESTLOEBETID_FCT t7 ON (t1.KILDEFACT_Key_I = t7.RESTLOEBETID_FCT_I)
      WHERE t5.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' 
        AND t4.SKEMA_KODE = 'C 73.00' 
        AND t2.PERIODAG_D = (
            SELECT MAX(t3.PERIODAG_D)
            FROM COREPLNZ.KXYZ1000FCT t1
                 LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t2 ON (t1.SKEMA_XYZ_JUNK_I = t2.SKEMA_XYZ_JUNK_I)
                 LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t4 ON (t1.KILDEFACT_I = t4.KILDEFACT_I)
                 LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t3 ON (t1.OPGOR_DAG_I = t3.PER_DAG_I)
            WHERE t4.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' AND t2.SKEMA_KODE = 'C 73.00'
          )
      ORDER BY t2.PER_DAG_I DESC;
QUIT;

2. 使用PROC SORT + 条件筛选直接获取最新批次数据 #

如果数据量较大，PROC SORT的效率可能优于SQL子查询。先筛选关联数据，再按日期降序排序，最后提取最新日期的所有记录：

/* 关联所有表并筛选基础条件 */
PROC SQL;
   CREATE TABLE WORK.JOINED_DATA AS
   SELECT t2.PERIODAG_D AS OpgoerelseDato, 
          t6.PERIODAG_D AS AfviklingDato, 
          t1.KILDEFACT_Key_I, 
          t3.X_ORDINATE, 
          t3.Y_ORDINATE, 
          t3.Z_ORDINATE, 
          t4.SKEMA_KODE, 
          t4.SKEMA_NAVN, 
          t5.KILDEFACT_NAVN, 
          t7.KUNDE_SCD_I, 
          t7.KONTO_SCD_I, 
          t2.PER_DAG_I
   FROM COREPLNZ.KXYZ1000FCT t1
        LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t2 ON (t1.OPGOR_DAG_I = t2.PER_DAG_I)
        LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t6 ON (t1.AFVIKL_DAG_I = t6.PER_DAG_I)
        LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t3 ON (t1.SKEMA_XYZ_JUNK_I = t3.SKEMA_XYZ_JUNK_I)
        LEFT JOIN COREPLNZ.TXYZ0100_SKEMA_DIM t4 ON (t1.SKEMA_I = t4.SKEMA_I)
        LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t5 ON (t1.KILDEFACT_I = t5.KILDEFACT_I)
        LEFT JOIN COREPLNZ.KLIK6000_RESTLOEBETID_FCT t7 ON (t1.KILDEFACT_Key_I = t7.RESTLOEBETID_FCT_I)
   WHERE t5.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' AND t4.SKEMA_KODE = 'C 73.00';
QUIT;

/* 按日期降序排序 */
PROC SORT DATA=WORK.JOINED_DATA OUT=WORK.SORTED_DATA;
   BY DESCENDING OpgoerelseDato;
RUN;

/* 提取最新日期的所有数据 */
DATA WORK.QUERY_FOR_TXYZ1000FCT_000F;
   SET WORK.SORTED_DATA;
   IF _N_ = 1 THEN RETAIN LATEST_DATE;
   IF _N_ = 1 THEN LATEST_DATE = OpgoerelseDato;
   IF OpgoerelseDato = LATEST_DATE;
RUN;

3. 简化宏变量生成步骤，减少中间表 #

如果必须使用宏变量，可以直接在一个PROC SQL步骤中完成最新日期的获取和宏变量赋值，无需创建中间表：

PROC SQL NOPRINT;
   SELECT MAX(t3.PERIODAG_D) INTO :LATEST_TIMESTAMP
   FROM COREPLNZ.KXYZ1000FCT t1
        LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t2 ON (t1.SKEMA_XYZ_JUNK_I = t2.SKEMA_XYZ_JUNK_I)
        LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t4 ON (t1.KILDEFACT_I = t4.KILDEFACT_I)
        LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t3 ON (t1.OPGOR_DAG_I = t3.PER_DAG_I)
   WHERE t4.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' AND t2.SKEMA_KODE = 'C 73.00';
QUIT;

/* 使用宏变量筛选最新数据 */
PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_TXYZ1000FCT_000F(label="WORK.QUERY_FOR_TXYZ1000FCT_000F") AS 
   SELECT t2.PERIODAG_D AS OpgoerelseDato, 
          t6.PERIODAG_D AS AfviklingDato, 
          t1.KILDEFACT_Key_I, 
          t3.X_ORDINATE, 
          t3.Y_ORDINATE, 
          t3.Z_ORDINATE, 
          t4.SKEMA_KODE, 
          t4.SKEMA_NAVN, 
          t5.KILDEFACT_NAVN, 
          t7.KUNDE_SCD_I, 
          t7.KONTO_SCD_I, 
          t2.PER_DAG_I
      FROM COREPLNZ.KXYZ1000FCT t1
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t2 ON (t1.OPGOR_DAG_I = t2.PER_DAG_I)
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t6 ON (t1.AFVIKL_DAG_I = t6.PER_DAG_I)
           LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t3 ON (t1.SKEMA_XYZ_JUNK_I = t3.SKEMA_XYZ_JUNK_I)
           LEFT JOIN COREPLNZ.TXYZ0100_SKEMA_DIM t4 ON (t1.SKEMA_I = t4.SKEMA_I)
           LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t5 ON (t1.KILDEFACT_I = t5.KILDEFACT_I)
           LEFT JOIN COREPLNZ.KLIK6000_RESTLOEBETID_FCT t7 ON (t1.KILDEFACT_Key_I = t7.RESTLOEBETID_FCT_I)
      WHERE t5.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' AND t4.SKEMA_KODE = 'C 73.00' AND t2.PERIODAG_D = &LATEST_TIMESTAMP
      ORDER BY t2.PER_DAG_I DESC;
QUIT;

内容的提问来源于stack exchange，提问作者Christoffer