Please help improve the query.

[Kurt_Bremser]

DATEPART is obviously not translated to an Oracle equivalent.

Hint: avoid SAS function calls in WHERE conditions which could be executed on the DB side.

where Tr_Kovetz > "01jan2023:00:00:00"dt 

should enable SAS to translate the datetime literal to Oracle syntax and push the WHERE to the database.

What are the attributes of 

a.l_pol_no

? 

[LinusH]

Just a side note, by using SQL_FUNCTIONS=ALL, the datepart() function *should* be passed to Oracle.
But using a datetime constant is better regardless 🙂

[IgorR]

Many thanks for the Hint, I will  use this in my work.

l_pol_no  - is numeric, length (in bytes) 8, format 10.

I hope this is what you mean "attributes".

[Patrick]

---

@IgorR wrote:

Many thanks for the Hint, I will  use this in my work.

l_pol_no  - is numeric, length (in bytes) 8, format 10.

I hope this is what you mean "attributes".

---

Below the code version for a numeric l_pol_no.

Given that you shared your real SQL you might be lucky and the code after comment  /** load data into SAS reading from DB in slices **/ works almost as-is in your environment. I've also added a variable "version" because I assumed you will need this information for further processing. You would need to rename this variable to the name it got in your actual source table (or remove it from the code if not needed).

I couldn't test it but the generated SQL should execute fully in-database and only data with matching keys to your SAS table should get transferred to the SAS side.

/** create sample data **/
options spool msglevel=i dlcreatedir sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname dds "%sysfunc(pathname(work))/dds";
%let n_policies=18000;

data work.Data_Prep;
  length l_pol_no 8;
  do l_pol_no=1 to &n_policies by 2;
    output;
  end;
run;

data dds.Versions;
  length ms_pol 8 version 8 Tr_Kovetz Ms_Girsa 8;
  format Tr_Kovetz datetime20.;
  Ms_Girsa=_n_;
  do ms_pol=1 to &n_policies;
    do version=rand('integer',1,200) to 1 by -1;
      Tr_Kovetz=datetime()-86400*version;
      output;
    end;
  end;
run;

/** load data into SAS reading from DB in slices **/
proc datasets lib=work nolist nowarn;
  delete versions:;
run;quit;

filename codegen temp;
data _null_;
  file codegen;
  set work.Data_Prep(keep=l_pol_no) end=last;
  length pol_list $32020;
  retain pol_list;
  retain slice 1;

  pol_list=catx(',',pol_list,put(l_pol_no,f16. -l));
  
  if last or length(pol_list)>=32000 then
    do;
      put
        'proc sql;                                  ' /
        '  create table work.Versions_' slice 'as   ' /
        '    select                                 ' /
        '      ms_pol                               ' /
        '      ,version                             ' /
        '      ,Tr_Kovetz                           ' /
        '      ,MS_Girsa                            ' /
        '    from dds.Versions                      ' /
        '    where                                  ' /
        '      Tr_Kovetz >= "01jan2023 00:00:00"dt  ' /
        '      and                                  ' /
        '      ms_pol in (' pol_list ')             ' /
        '    ;                                      ' /
        'quit;                                      ' /
        ;

      slice+1;
      call missing(pol_list);

    end;
run;
%include codegen / source2;
/*%include codegen / nosource2;*/

/** combine slices **/
data work.versions;
  set work.versions_:;
run;

/** join as per OP but only with subset loaded from DB **/
Proc SQL;
  Create Table Mispar_Girsat_Pol As
    Select
      a.*
      ,b.Ms_Girsa
      ,b.version
      ,b.Tr_Kovetz
    From Data_Prep As a
      Left Join WORK.Versions As b
        On a.l_pol_no = b.Ms_pol
      Where 
        Datepart(Tr_Kovetz) > "01jan2023"d 
      Order by
        a.l_pol_no,
        b.Ms_Girsa;
Quit;

The SQL above code generates looks like:

141       +proc sql;
142       +  create table work.Versions_4 as
143       +    select
144       +      ms_pol
145       +      ,version
146       +      ,Tr_Kovetz
147       +      ,MS_Girsa
148       +    from dds.Versions
149       +    where
150       +      Tr_Kovetz >= "01jan2023 00:00:00"dt
151       +      and
152       +      ms_pol in
152      !+(17455,17457,17459,17461,17463,17465,17467,17469,17471,17473,17475,17477,17479,17481,17483,17485,17487,17489,17491,17493,
152      !+17495,17497,17499,17501,17503,17505,17507,17509,17511,17513,17515,17517,17519,17521,17523,17525,17527,17529,17531,17533,1
152      !+7535,17537,17539,17541,17543,17545,17547,17549,17551,17553,17555,17557,17559,17561,17563,17565,17567,17569,17571,17573,17
152      !+575,17577,17579,17581,17583,17585,17587,17589,17591,17593,17595,17597,17599,17601,17603,17605,17607,17609,17611,17613,176
152      !+15,17617,17619,17621,17623,17625,17627,17629,17631,17633,17635,17637,17639,17641,17643,17645,17647,17649,17651,17653,1765
152      !+5,17657,17659,17661,17663,17665,17667,17669,17671,17673,17675,17677,17679,17681,17683,17685,17687,17689,17691,17693,17695
152      !+,17697,17699,17701,17703,17705,17707,17709,17711,17713,17715,17717,17719,17721,17723,17725,17727,17729,17731,17733,17735,
152      !+17737,17739,17741,17743,17745,17747,17749,17751,17753,17755,17757,17759,17761,17763,17765,17767,17769,17771,17773,17775,1
152      !+7777,17779,17781,17783,17785,17787,17789,17791,17793,17795,17797,17799,17801,17803,17805,17807,17809,17811,17813,17815,17
152      !+817,17819,17821,17823,17825,17827,17829,17831,17833,17835,17837,17839,17841,17843,17845,17847,17849,17851,17853,17855,178
152      !+57,17859,17861,17863,17865,17867,17869,17871,17873,17875,17877,17879,17881,17883,17885,17887,17889,17891,17893,17895,1789
152      !+7,17899,17901,17903,17905,17907,17909,17911,17913,17915,17917,17919,17921,17923,17925,17927,17929,17931,17933,17935,17937
152      !+,17939,17941,17943,17945,17947,17949,17951,17953,17955,17957,17959,17961,17963,17965,17967,17969,17971,17973,17975,17977,
152      !+17979,17981,17983,17985,17987,17989,17991,17993,17995,17997,17999 )
153       +    ;
NOTE: Table WORK.VERSIONS_4 created, with 28438 rows and 4 columns.

154       +quit;

[LinusH]

@Patrick, isn't DBKEY doing just this OOTB?

[Patrick]

---

@LinusH wrote:
@Patrick, isn't DBKEY doing just this OOTB?

---

@LinusH  I didn't have an environment to test and wasn't 100% sure if SAS will generate such code if there is another variable involved (Tr_Kovetz >= "01jan2023 00:00:00"dt) and I also wasn't sure if SAS would generate such SQL if the distinct values don't fit into a single SQL. Oracle has to my knowledge a 32KB limit for a single SQL so there is a need for multiple queries for 9000 individual policies.

@IgorR Using DBKEY is certainly worth a test as it would allow for much simpler coding. Please let us know what works for you.

[IgorR]

Hi, The code is working great. Thank you! But I'm trying to understand how this code works. How does it decide how many "slices" to create? And what is the size of each slice?

[Kurt_Bremser]

The slice size is given as 32000 characters for the IN list. Every time this size is reached, the current slice code is written to the file, the slice count incremented, and the list cleared. The actual number of slices is irrelevant, as they are all combined later by using a wildcard instead of slice numbers.