I am a beginnner in sas and this is my first report. I have a daily report that runs on weekdays, capturing payments made through TYPE1 by customers with no previous history of that payment type. The table retention is 6 years, so I go back to 2018 to capture the history data, and a week back to capture current data. Then I use merge statement to get the accounts that has no history of type1 payments. this to capture customers with any history of TYPE1 payment.
dataset master;
proc sql;
connect to oracle;
create table master as
select * from conections to oracle(
select * from table1 where
cust_open_date >= '2018-01-01' and transaction_dt < Trunc(sysdate -7);
disconnect from oracle;
quit;
dataset subset;
proc sql;
connect to oracle;
create table master as
select * from conections to oracle(
select * from table1 where
cust_open_date >= '2018-01-01' and transaction_dt >= Trunc(sysdate -7);
disconnect from oracle;
quit;
data merge_data;
merge master (in=a) subset (in=b);
by acct_id;
if b and not a;
run;
Issue: It runs fine on day 1, but when it rus on day 2, it has duplicates from day 1 along with results from day2. So to avoid that, I imported day1, compared with day2 and removed duplicates . this worked fine for few days. But again, when it runs for 3 day, and after removing duplicates there is no new data, the code goes and fetches day1 output.
I would like to see if there is any way I can avoid duplicates from previous day files as i run it everyday.
I also tried, importing last 5 days of file using &previous_day macro, but then that fails if one of the day has NO data in it.
Appreciate your time and help.