Getting only the newest data from a table in SAS EG in an efficient way-CodePudding

I have a table which is updated at irregular intervals; I need to always retrieve the newest data set from the table.

I will know the data is the newest if the column PERIODAG_D (timestamp vraiable) is close to the current date.

My current solution is to set outobs=1 to only get one observation and order by PER_DAG_I (numeric date variable) decending:

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;

This gives me the following output:

I then add PERIODAG_D and PER_DAG_I to macro variables I can use in my WHERE statement to get the newest data in the table.

My issue is this feels like a very inefficient workaround to only get a date variable.

I hope you can point me in the right direction.

EDIT to show the creation of the macro variable and how it is used:

This is how I create the macro variable:

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;

This is the code Insert my macro variable &str_SAStimestamp_Desc_sort into:

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;

CodePudding user response：

You can use into in a proc sql statement to save the result to a macro variable:-

PROC SQL NOPRINT;
SELECT MAX(my_date)
INTO :my_date_variable
FROM (sql_statement_returning_mydates);
QUIT;

Check the macro variable with %PUT &my_date_variable;

Then use the macro variable in your statement.

WHERE date_column = "&my_date_variable"d

The attached are useful:-

Using the Magical Keyword 'INTO:' in PROC SQL

youtube: Date as SAS Macro variable – common issues and solutions

Good luck

CodePudding user response：

Try using the HAVING max(t2.PERIODAG_D) = t2.PERIODAG_D clause like below.

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
      HAVING max(t2.PERIODAG_D) = t2.PERIODAG_D
      ORDER BY t2.PER_DAG_I DESC;
QUIT;