Things take so long because your current syntax transfers all rows from the DB to SAS prior to subsetting the rows.
What you need to do is write syntax that reduces the volume of data transferred between the database and SAS.
With the SAS table being the low volume you need to upload your SAS table to the DB (or at least the keys) and then only load the much smaller result set back to SAS.
To do so you need either to load the SAS data into a DB table (temporary or not) or you need to generate a SQL where clause (where key in (<list of values>).
The latter option would certainly require generation of multiple SQL statements to fit all the 9000 values.
The "next best option" would be to at least reduce the volume on the DB side prior to loading into SAS (=above select with some where clause in it, mainly on date and version).
Questions
1. What is the database (need to know for DB specific explicit SQL syntax)
2. Do you really need all versions or only the most recent one?
You wrote that you've got around 9000 rows in your SAS table. The result set you get back has 821303 rows which means there are around 90 versions per policy. Does that sound about right to you?
If you need multiple versions would there be some logic to at least drop some of the versions prior to loading to the SAS side?
And last but not least: You don't need to be a DBA to get Create/Drop table permissions. That's something SAS developers at many sites are been given. It doesn't need to be in the same database schema where your payload tables are stored. At least getting permissions to create temporary tables should be given. Where and how exactly will depend on the database (so... which database are you using).
...actually: The log you shared tells us that it's Oracle.
Loading the SAS data first to the database for the join would allow for much simpler code than below.
What below does is generate SQL code where the keys from the SAS table get passed to the DB within the where clause as hardcoded values. Because this list of values wouldn't fit into a single SQL the code generates multiple SQL to query the DB multiple times and then just combines the data on the SAS side afterwards.
/** 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(drop=_:);
length l_pol_no $16;
do _i=1 to &n_policies by 2;
l_pol_no=put(_i,z16.);
output;
end;
run;
data dds.Versions(drop=_:);
length ms_pol $16 version 8 Tr_Kovetz Ms_Girsa 8;
format Tr_Kovetz datetime20.;
Ms_Girsa=_n_;
do _i=1 to &n_policies;
ms_pol=put(_i,z16.);
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 $32000;
retain pol_list n_vals;
if _n_=1 then
do;
slice=1;
n_vals=floor(32000/(vlength(l_pol_no)+3));
end;
pol_list=catx(',',pol_list,cats("'",l_pol_no,"'"));
if last or mod(_n_,n_vals) =0 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;
For performance: If ms_pol needs to come first or second in the query depends on the indexes on your Oracle table. Given what you described I'm hoping your DBA partitioned the table by date (tr_kovetz) and that's why I have this variable first in the where clause.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
2672 views
-
2 likes
-
-
