Hello I have a data set with 60 million rows and 850 variables. In this data set each customerID has 24 rows (Each row represent information of specific month). I want to select customers that meet the condition that in month Nov 2021 they belong to population 7. Then ,for this customer list I want to check for each customer if he/she failed to pay loans in months Jul2022 till Oct2022. Then I want to calculate how many customers failed to pay loans in months jul2022 till oct2022.( if customer fail at least in one month ). I created sql query that calculate it. Since the data set is very big then I created a small data set (1 million rows) and run the query and in 1 minute I got the result. However, when I run it on full data set then I waited long time and finally got a message that it was terminated.
Then I run it again but didnt recieve any result .
Here is the code I run. Any recommendation how to run code to receive the desired result?
proc sql;
create table want as
select count(*) as nr
from
(select lakoach
from csp.twoyrs_3
where VALID_FROM_DTTM='01NOV2022'd and pop_lomeforat_wppr=7 ) as a
inner join
(select lakoach,
max(ind_shlili_6) as calc_ind_shlili_1_5
from csp.twoyrs_3
where VALID_FROM_DTTM in ('01OCT2022'd,'01SEP2022'd ,'01AUG2022'd,'01JUL2022'd,'01JUN2022'd)
group by lakoach
having ind_shlili_6=1) as b
on a.lakoach=b.lakoach
;
quit;
Here is the Log when I run and waited long time and didnt get any result
1 The SAS System 13:07 Tuesday, September 3, 2024
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='IV dist';
5 %LET _CLIENTPROJECTPATH='K:\aתחום מודלים\פיתוח מודל CS\פיתוח מודלים\עוסקים_7\Project_Models_Oskim_Ron_Gibuy.egp';
6 %LET _CLIENTPROJECTPATHHOST='VSK1H103A2014';
7 %LET _CLIENTPROJECTNAME='Project_Models_Oskim_Ron_Gibuy.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 proc sql;
27 create table ttt2a as
28 select count(*) as nr
29 from
30 (select lakoach
31 from csp.twoyrs_3
32 where VALID_FROM_DTTM='01NOV2022'd and pop_lomeforat_wppr=7 ) as a
33 inner join
34 (select lakoach,
35 max(ind_shlili_6) as calc_ind_shlili_1_5
36 from csp.twoyrs_3
37 where VALID_FROM_DTTM in ('01OCT2022'd,'01SEP2022'd ,'01AUG2022'd,'01JUL2022'd,'01JUN2022'd)
38 group by lakoach
39 having ind_shlili_6=1) as b
40 on a.lakoach=b.lakoach
41 ;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Compression was disabled for data set WORK.TTT2A because compression overhead would increase the size of the data set.
NOTE: SAS threaded sort was used.
... View more
