Hi,
Since I can't edit and update my post anymore, I do it here.
Here is Actual code:
options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix; Proc SQL;
Create Table Mispar_Girsat_Pol As
Select
a.*,
b.Ms_Girsa
From Data_Prep As a
Left Join dds.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;
@Kurt_Bremser , I added "When " class to make the query "lighter", but it is not perfect cause it might cause missing data.
I will explain the purpose of the code.
I have a list of insurance policies (Data_Prep Table).
I need to match to every policy all it's versions , that data about the versions is stored in the table dds.Versions.
Table dds.Versions is huge because every two or tree months they add to this table new data that include information about every changes that was made in policies. The date of updating the Table is stored in variable "Tr_Kovetz".
If I choose specific Date in "Tr_Kovetz" I might miss information about versions numbers that are not included in this date, thus I need to get all versions from previous dates.
Variable "Ms_Girsa" is a number of policy version. To each policy there can be many versions.
But, this is only a start. I might to add another variables from dds.Versions.
@LinusH gave me a hint that i used and here is a LOG of a program (That succeeded, but it took too much time):
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Add_New_Params';
4 %LET _CLIENTPROCESSFLOWNAME='New factors';
5 %LET
5 ! _CLIENTPROJECTPATH='CLASSIFIED\ACTUARY\Igorr\PHI\Phi_In_Pay_ver7_new_tables_
5 ! 062023.egp';
6 %LET _CLIENTPROJECTPATHHOST='PC0692';
7 %LET _CLIENTPROJECTNAME='Phi_In_Pay_ver7_new_tables_062023.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HTMLBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
29
30 Proc SQL;
31 Create Table Mispar_Girsat_Pol As
32 Select
33 a.*,
34 b.Ms_Girsa
35 From Data_Prep As a
36 Left Join dds.Versions As b
37 On a.l_pol_no = b.Ms_pol
38 Where
39 Datepart(Tr_Kovetz) > "01jan2023"d
40 Order by
41 a.l_pol_no,
42 b.Ms_Girsa;
ORACLE_1: Prepared: on connection 5
SELECT * FROM DDS.VERSIONS
ORACLE_2: Prepared: on connection 5
SELECT NULL as TABLE_QUALIFIER, t.owner as TABLE_OWNER, t.table_name as TABLE_NAME, t.num_rows as ROW_COUNT FROM all_tables t
WHERE t.table_name='VERSIONS' AND t.owner='DDS'
2 The SAS System 12:30 Tuesday, September 19, 2023
ORACLE_3: Executed: on connection 5
SELECT statement ORACLE_2
NOTE: SAS threaded sort was used.
ORACLE_4: Prepared: on connection 5
SELECT "MS_POL", "MS_GIRSA", "TR_KOVETZ" FROM DDS.VERSIONS
ORACLE_5: Executed: on connection 5
SELECT statement ORACLE_4
NOTE: Table WORK.MISPAR_GIRSAT_POL created, with 821303 rows and 6 columns.
43 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 2:08:05.03
cpu time 1:40:26.25
44
45 %LET _CLIENTTASKLABEL=;
46 %LET _CLIENTPROCESSFLOWNAME=;
47 %LET _CLIENTPROJECTPATH=;
48 %LET _CLIENTPROJECTPATHHOST=;
49 %LET _CLIENTPROJECTNAME=;
50 %LET _SASPROGRAMFILE=;
51 %LET _SASPROGRAMFILEHOST=;
52
53 ;*';*";*/;quit;run;
54 ODS _ALL_ CLOSE;
55
56
57 QUIT; RUN;
I hope this information will give you a hints.
Thanks in advance.
... View more
