topic Re: SAS query in SAS Programming

SAS query

scb — Fri, 13 Apr 2018 09:24:00 GMT

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.

Re: SAS query

RW9 — Fri, 13 Apr 2018 10:05:47 GMT

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

Re: SAS query

SuryaKiran — Fri, 13 Apr 2018 12:43:50 GMT

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;

Re: SAS query

novinosrin — Fri, 13 Apr 2018 14:55:56 GMT

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;