Querying historical data

[alepage]

Hello,

I am executing a SQL query in SAS while using a Snow Flake connector.

in Snow Flake the Query will be like that (omitting the SAS connector in this example):

proc sql;
SELECT * 
FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::timestamp_tz);
quit;

I have tried this example in SAS

data my_table;
set sashelp.class;
run;

proc sql;
SELECT * 
FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::timestamp_tz);
quit;

And I am getting the following error.

What will be the equivalent is SAS ?

32 proc sql;
33 SELECT *
34 FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::timestamp_tz);
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

[Kurt_Bremser]

Please describe in plain language what the query should do.

[Patrick]

I copy/pasted your SQL into Copilot for an explanation. 
Copilot believes your Snowflake syntax should use to_timestamp() and look similar to below:

SELECT *
FROM my_table AT(TIMESTAMP => TO_TIMESTAMP_TZ('2024-06-26 09:20:00 -07:00'));

This syntax is Snowflake specific without an equivalent in SAS SQL. You need to use explicit pass-through SQL out of SAS. Syntax like below should work (not tested).

libname mylib snowflake .......;
proc sql;
  connect using mylib;
  select *
  from connection to mylib(
    SELECT *
    FROM my_table AT(TIMESTAMP => TO_TIMESTAMP_TZ('2024-06-26 09:20:00 -07:00'))
  );
quit;
  

The way Snowflake tables maintain history and how you can query it, is afaik Snowflake specific. It's different for other databases and SAS. 

SAS doesn't maintain change history in tables (SAS files) per default and you would need to define an audit trail with the table to get something similar Understanding an Audit Trail
In my opinion: If you need such auditing on a table then better store your data in a DB like Snowflake. In my experience if there are requirements like this (i.e. regulatory reqs) then it's normally easier and more robust to implement them using existing DB functionality.

[alepage]

More in details, what I need to do is to run the SAS script today and five days ago and to check if the result of the SQL query gives the same results.

%MACRO count_classic_prop_edpplatform(CIE);

/********** UAT environment *****************/
 
LIBNAME CNTPDMU SASIOSNF  
DATABASE=CONTRACTPL_UAT_DB_GLD  
SERVER=
SCHEMA=CONTRACTPL_DM
AuthDomain="SNOWFLAKE_CONTRACTPL_DM_UAT";
run;



/****** The SQL Query ******************/

PROC SQL;
	CREATE TABLE temp.inforce_prop_contact_&suffix. As
	SELECT
           contractVer.POL_NBR          as policy,
           contractVerOriginal.TRM_NBR,
           contractVerOriginal.TRM_VER_NBR,
           riskVer_RE.REF_NBR AS RSK_REF_NBR,

           contractVer.CNTRCT_EFF_DT,
           contractVer.CNTRCT_EXPRY_DT,
           contractVerOriginal.TRX_EFF_DT,
           contractVer.TRX_TYP_CD,
           factPremium.OFFSET_IND,
           factPremium.TRM_PREM_AMT     as FTPREM,
           contractVer.MAIN_INSRR_CO_NBR,
           contractVer.CTRLG_PROV_ID    as province,
           contractVer.BRNCH_REF_NBR,
           contractVer.DSTRBTR_NBR,
           contractVer.ORIG_EFF_DT,
           contractVer.PREFRD_CNTRCT_LOLTY_DT,
           contractVer.MHA_ELGBLTY_IND,
           contractVer.MRKT_TRTRY_REF_NBR,
           roleVer_INSRD.PR_INSRR_CD,
           contractVer.INTRO_CD ,
           riskVer_RE.LOB_CD,
           contractVer.CNTRCT_TYP_CD,
           contractVerOriginal.AUTHRSTN_DT,
           Temptbl.DIM_PL_CNTRCT_VER_KEY,
	   Temptbl.LKP_PROGRAM_KEY,
	   Temptbl.DIM_PL_ROLE_VER_INSRD_KEY,
	   Temptbl.PROG_NM_CD,
	   Temptbl.PROG_CAT_CD,
	   coalesce(Temptbl.Affinity_Ind,'N') as Affinity_Ind
FROM cntrctDM.FCT_PL_COV_DRVR_PREM factPremium

          JOIN cntrctDM.DIM_PL_CNTRCT_VER contractVer
            ON factPremium.DIM_PL_CNTRCT_VER_KEY = contractVer.DIM_PL_CNTRCT_VER_KEY
          JOIN cntrctDM.DIM_PL_CNTRCT_VER contractVerOriginal
            ON factPremium.ORGNL_DIM_PL_CNTRCT_VER_KEY = contractVerOriginal.DIM_PL_CNTRCT_VER_KEY

          JOIN cntrctDM.DIM_PL_RSK_VER_RE riskVer_RE
            ON factPremium.DIM_PL_RSK_VER_RE_KEY = riskVer_RE.DIM_PL_RSK_VER_RE_KEY

	   JOIN cntrctDM.DIM_PL_COV_VER covVer_PRMRY
	     ON factPremium.PRMRY_DIM_PL_COV_VER_KEY = covVer_PRMRY.DIM_PL_COV_VER_KEY

          JOIN cntrctDM.DIM_PL_ROLE_VER_INSRD roleVer_INSRD
            ON factPremium.DIM_PL_ROLE_VER_INSRD_KEY = roleVer_INSRD.DIM_PL_ROLE_VER_INSRD_KEY

 	  LEFT JOIN Temptbl 
	  ON (Temptbl.DIM_PL_CNTRCT_VER_KEY=contractVer.DIM_PL_CNTRCT_VER_KEY)
     WHERE
           contractVer.CNTRCT_TYP_CD not in ('4','5') and
           factPremium.DIM_PL_RSK_VER_RE_KEY > -1 AND
		   /* Eliminating shell transactions */
		   contractVer.TRX_TYP_CD NE "SHL" AND

		   /* Selecting policies possibly in force based on CNTRCT_EFF_DT,CNTRCT_EXPRY_DT, and 
		      (INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1)*/
 
		   contractVer.CNTRCT_EFF_DT      <= (INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1) AND
           contractVer.CNTRCT_EXPRY_DT     > (INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1) AND
           contractVerOriginal.TRX_EFF_DT <= (INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1) AND

           /*contractVer.POL_NBR in (&prop_policy_list.) and*/

		   /*** risk when the policy is renewed that should not be considered inforce CHG74888 ****/

		   
			   (Not(covVer_PRMRY.MODIF_CD = 'S' And factPremium.WPRM_AMT = 0) And 
				Not(contractVerOriginal.TRX_TYP_CD = 'RWL' And factPremium.OFFSET_IND='Y')
			   ) AND


           /* Filtering condition to obtain only the transactions from Belair */

           %if &cie=be %then 
	       %do;
               contractVer.MAIN_INSRR_CO_NBR = "010"
           %end;           
        
         /* Filtering conditions to obtain only the transactions from Intact Quebec */

	     	%else %if &cie=gc %then 
	          %do;
		       		contractVer.MAIN_INSRR_CO_NBR = "001" and
               	 	contractVer.CTRLG_PROV_ID="QC"
	     	  %end;

     ORDER BY
           contractVer.MAIN_INSRR_CO_NBR, contractVer.CTRLG_PROV_ID, policy,
           riskVer_RE.REF_NBR, contractVer.TRM_NBR, contractVer.TRM_VER_NBR, factPremium.OFFSET_IND DESC
       ;
   QUIT;

%put ===================== End of count_classic_prop_edpplatform ==== Company =&cie. =================================================== ;

%exit:
%MEND count_classic_prop_edpplatform;
%count_classic_prop_edpplatform(gc);

by using the time travel on a Snow Flake database but in SAS

How do we do that ?

Provide an example because up to now we are using a libname to connect to the good data base not a pass through connection

[Patrick]

More in details, what I need to do is to run the SAS script today and five days ago and to check if the result of the SQL query gives the same results.

I believe what you're trying to say: You need to run the SAS script twice today, once using data as per now, and once using data as they were five days, compare the result of the two runs and determine if there are any differences.

Observations:

1. Libref CNTPDMU defined in the libname you shared doesn't match libref cntrctDM used in the SQL
   
   1. I assume cntrctDM is also a libref pointing to Snowflake?
2. Temptbl in the join is a SAS table.'
   
   1. Where does it get created?
   2. Does it also use a Snowflake data source? If not could it change within your 5 days time window?

If you can't construct Temptbl as it would have looked 5 days ago then you won't be able to get what you want at all.

For the rest: As I wrote previously, you will need to pull the "5 days ago" state of your Snowflake tables via Snowflake SQL using explicit pass-through SQL and not like in your code implicit pass-through SQL that SAS then translates and sends to Snowflake for execution. And the reason for this is still: SAS SQL doesn't have a function for time travel functionality. 

Provide an example because up to now we are using a libname to connect to the good data base not a pass through connection

The example I've shared already demonstrates how you can use a SAS libref for explicit pass-through SQL.

[LinusH]

@Patrick showed you how to connect to Snowflake within PRO SQL.

For your query (if you wish to store the result in Snowflake), you should use the

execute( your SQL-statements goes here ) by snowflake;

construct.

[Quentin]

This snowflake time travel feature looks cool.  Agree with others, you'll likely need to use explicit pass through so that you can submit native snowflake SQL from a SAS job.  When you use implicit pass through, SAS translates that SAS SQL into Snowflake SQL for you.  But there are plenty of features in Snowflake which do not have SAS equivalents.  To access those, you need to write in Snowflake, not SAS.