DATA Step, Macro, Functions and more

SAS query

Reply
Frequent Contributor
Frequent Contributor
Posts: 87

SAS query

[ Edited ]

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.

Super User
Super User
Posts: 9,866

Re: SAS query

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

PROC Star
Posts: 632

Re: SAS query

[ Edited ]

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;
Thanks,
Suryakiran
Super User
Posts: 2,073

Re: SAS query

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;
Ask a Question
Discussion stats
  • 3 replies
  • 134 views
  • 0 likes
  • 4 in conversation