@User_2024 Based on what you've told me below some "template" sample code to show how logic could work only using your one main table in Oracle.
I would only switch to something more "complicated" for performance reasons - like maintaining a separate table with customer ids selected in earlier runs instead of working this out every single time like in below query.
options dlcreatedir;
libname ora "%sysfunc(pathname(work))/oracle";
libname ctrl "%sysfunc(pathname(work))/perm_storage";
/* master source table with all transactions */
data ora.master;
infile datalines truncover dlm=',';
input trans_id acct_id$ cust_id$ trans_type trans_dttm:anydtdtm.;
format trans_dttm datetime20.;
datalines;
1,a,x,1,01jan2024
2,a,x,2,01feb2024
3,b,x,1,01mar2024
2,a,x,2,02mar2024
2,b,x,2,02mar2024
;
/* control table to store max transaction date selected in a previous run */
/* - needs to be a permanent table
- create table if it doesn't exist
*/
%if not %sysfunc(exist(ctrl.last_selected)) %then
%do;
data ctrl.last_selected;
attrib run_dttm max_trans_dttm format=datetime20.;
stop;
run;
%end;
/* &prev_max_trans_dttm: max transaction date already selected in a previous run */
proc sql noprint;
select coalesce(max(max_trans_dttm),0) format=datetime20. into :prev_max_trans_dttm trimmed
from ctrl.last_selected
;
quit;
/* create data for reporting:
- only include transactions of type 1
- only select transaction dates > &prev_max_trans_dttm
- exclude transactions that already had been selected in earlier runs
= transactions for customers that already had a type 1 transaction at or before &prev_max_trans_dttm
*/
proc sql;
create table work.report_source as
select cust_id, trans_type, trans_dttm
from
(
select cust_id, trans_type, trans_dttm
from ora.master
where trans_dttm>"&prev_max_trans_dttm"dt and trans_type=1
group by cust_id
having max(trans_dttm)=trans_dttm
)
where not exists
(
select distinct cust_id
from ora.master
where trans_dttm<="&prev_max_trans_dttm"dt and trans_type=1
)
;
quit;
/* add most current max transaction date selected to control table */
proc sql;
insert into ctrl.last_selected
set run_dttm=datetime(), max_trans_dttm=max("&prev_max_trans_dttm"dt,(select max(trans_dttm) from report_source))
;
quit;
/* and here just some "template" to give you an idea how to deal with a zero row result */
data _null_;
call symputx('nobs',nobs);
stop;
set work.report_source nobs=nobs;
run;
%put &=nobs;
%if &nobs=0 %then
%do;
data _null_;
file print;
put "no rows selected";
run;
%end;
%else
%do;
proc print data=work.report_source;
run;
%end;
