SAS query

[scb]

TableA

Acct       Target_Dt

123456  13/2/2018

123561   30/12/2017

TableB

Acct       Spent_Dt           Amt

123456  1/12/2017         $20

123456  14/2/2018         $40

123456   10/4/2018        $20

123561    1/1/2017         $100

123561    30/12/2017     $110

123561    1/1/2018         $20

Desired Output

Acct       Target_Dt          Total_Amt

123456  13/2/2018          $60

123561   30/12/2017       $130

May I know how to get the total amount for acct that is the spent date is either equal or exceed target date?

Thanks.

[RW9]

If you can't be bothered to present test data in a runnable form as asked before, I can't be bothered to present runnable code to solve the issue, so all you get is:

merge table a and b based on acct

is target before spent then add number to retained variable

if last acct then output

[SuryaKiran]

Try this:

DATA TableA;
Format Target_Dt ddmmyy10.;
infile datalines dlm=" " ;
INPUT Acct Target_Dt ddmmyy10.;
Datalines;
123456 13/02/2018
123561 30/12/2017
;
run;
DATA TableB;
Format Spent_Dt ddmmyy10. Amt dollar8.;
infile datalines dlm=" " ;
INPUT Acct Spent_Dt ddmmyy10. Amt dollar8.;
Datalines;
123456 01/12/2017 $20
123456 14/02/2018 $40
123456 10/04/2018 $20
123561 01/01/2017 $100
123561 30/12/2017 $110
123561 01/01/2018 $20
;
run;

PROC SQL;
CREATE TABLE WANT as 
select t1.*,SUM(t2.Amt) format=dollar8. as Total_Amt
 from TableA t1,TableB t2
Where t1.Acct=t2.Acct and t2.Spent_Dt>=t1.Target_Dt
Group by t1.Acct,t1.Target_Dt;
quit;

[novinosrin]

DATA TableA;
infile datalines dlm=" " ;
INPUT Acct Target_Dt ddmmyy10.;
Format Target_Dt ddmmyy10.;
Datalines;
123456 13/02/2018
123561 30/12/2017
;
run;
DATA TableB;
infile datalines dlm=" " ;
INPUT Acct Spent_Dt ddmmyy10. Amt dollar8.;
Format Spent_Dt ddmmyy10. Amt dollar8.;
Datalines;
123456 01/12/2017 $20
123456 14/02/2018 $40
123456 10/04/2018 $20
123561 01/01/2017 $100
123561 30/12/2017 $110
123561 01/01/2018 $20
;
run;
data want;
merge TableA Tableb;
by acct;
if first.acct then Total_Amt=0;
if Spent_Dt>=Target_Dt then Total_Amt+Amt;
if last.acct;
drop Spent_Dt amt;
run;