@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;
