BookmarkSubscribeRSS Feed
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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.

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Meteorite | Level 14

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
novinosrin
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 785 views
  • 0 likes
  • 4 in conversation