topic Re: SQL pass through vs libname ODBC with unexpected results? in SAS Programming

SQL pass through vs libname ODBC with unexpected results?

novinosrin — Thu, 10 Dec 2020 18:13:46 GMT

SQL pass through vs libname ODBC with unexpected results? Requesting advise as to why, what and how to fix plz?


/*Pass through*/
proc sql noprint;
connect to odbc(dsn=MSPBDE_PROD);
create table work.COVID_FB as 
select * from connection to odbc
     (select 
      a.LN_NO,
	  a._RowStatus,
	  a._EffectiveDate,
	  a._ExpirationDate,
	  a.FP_CREAT_DT,
	  a.FP_STAT_CD,
	  a.FP_STAT_DT,
	  a.FP_PROMISE_QT,
	  a.FP_PLAN_TY,
	  a.FP_LST_PR_DU_DT

      from MSPODS.bde.FORBEARANCE_PLAN for system_time ALL as a
   WHERE a._RowStatus NOT IN ('PD') AND a.FP_PLAN_TY in('90','91','92','93'));
quit;

proc sql;
 select count(LN_NO) as c, count(distinct LN_NO) as c1
 from COVID_FB;
quit;

RESULTS:
c	           c1
134289	34866

/*Libname */
libname msp_ods odbc dsn=mspbde_prod qualifier=MSPODS schema=BDE;

data w;
 set msp_ods.FORBEARANCE_PLAN;
 WHERE _RowStatus NOT IN ('PD') AND FP_PLAN_TY in('90','91','92','93');
run;

proc sql;
 select count(LN_NO) as c
 from w;
quit;

RESULT:
c
34671

What's going on?

Re: SQL pass through vs libname ODBC with unexpected results?

ballardw — Thu, 10 Dec 2020 18:31:28 GMT

How many records returned for Work.Covid_FB and W?

from MSPODS.bde.FORBEARANCE_PLAN for system_time ALL as a WHERE a._RowStatus NOT IN ('PD') AND a.FP_PLAN_TY in('90','91','92','93')); quit;

I might guess that the pass through using "for system_time all" overrides a default behavior from the ODBC connection default.

Re: SQL pass through vs libname ODBC with unexpected results?

SASKiwi — Thu, 10 Dec 2020 18:32:52 GMT

Do a PROC COMPARE on the two extracted datasets to see if you are starting with the same data.

Re: SQL pass through vs libname ODBC with unexpected results?

novinosrin — Thu, 10 Dec 2020 18:33:17 GMT

passthrough results
c	c1
134289	34866



libname results
c
34671

Re: SQL pass through vs libname ODBC with unexpected results?

Kurt_Bremser — Thu, 10 Dec 2020 18:37:30 GMT

What does this:

for system_time ALL

in the pass-through do?

Re: SQL pass through vs libname ODBC with unexpected results?

novinosrin — Thu, 10 Dec 2020 18:57:41 GMT

Many thanks 'Sir'*3 for quick responses.

I'm afraid I do not know SQL server proprietary language yet and I inherited from somebody here.

I googled this https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver15

to see what it means -

"If you search for non-current row versions only, we recommend you query the history table directly as this will yield the best query performance. Use ALL when you need to query current and historical data without any restrictions."

If I am "comprehending correctly", all ln_no(loans) in forebearance table with its history is what I am after. I am sure this would make sense to you. I presume from the above definition the ALL keyword gives me everything. However, how to replicate the same with LIBNAME? Or is it some sort of a restricted snaps that libname engine cannot fetch while pass through could?

For some reason, I am glad my mind told me check this. Had I just went with Libname results, I would had to face a huge escalation. Gosh, I am breathing still.

Re: SQL pass through vs libname ODBC with unexpected results?

Tom — Thu, 10 Dec 2020 19:20:03 GMT

Never used SQL server but for Teradata temporal tables we just set-up views that included the equivalent option for accessing the historical records. So you might have a table or view named MYTABLE that shows the current state and than a view named MYTABLE_HISTORY that shows all records. Then you can reference the MYTABLE_HISTORY view through your libref and see all records.

Re: SQL pass through vs libname ODBC with unexpected results?

novinosrin — Thu, 10 Dec 2020 19:41:20 GMT

Sir @Tom BRILLIANT BRILLIANT BRILLIANT.!!!!!!!!!!!!! YES I AM SHOUTING. AGAIN- BRILLIANT!!!!!!!!!!!!

It's just unfair for Einsteins to be in SAS community. I I just can't thank you enough.

Okay, one follow from my team, Can you please give us a moment to enlighten to thoroughly understand the mechanics as what SQL pass through fetches in full while the libname couldn't. I will just send this link to my team mates and share the knowledge once I could have the privilege of yet another response please.

KUDOS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!