I have two tables:-
Table(A)= Sales made on each date
Table (A)
Date | Sales |
29/02/20 | 10 |
04/03/20 | 5 |
05/03/20 | 25 |
06/03/20 | 30 |
23/03/20 | 10 |
31/03/20 | 40 |
16/04/20 | 40 |
Table (B)
Date |
01/02/20 |
10/02/20 |
02/03/20 |
05/03/20 |
20/03/20 |
25/03/20 |
06/04/20 |
27/04/20 |
Now I want to compare dates of table A and dates of table B to create a cumulative sales according to dates in table (B).
This is the required output:-
Date | Cumulative Sales |
01/02/20 | 10 |
10/02/20 | 10 |
02/03/20 | 10 |
05/03/20 | 40 |
20/03/20 | 70 |
25/03/20 | 80 |
06/04/20 | 120 |
27/04/20 | 160 |
So basically I need to add all the sales made in table A up to the date mentioned in table B, so that I have cumulative sales up to each date mentioned in table B.
Hi,
seems like code below do what you need, but I have one doubt - should the cumulative for dates earlier than Feb29th be 10? or 0 since first sales is on Feb29th. In case I'm right and it should be 0 then un-comment "/*2*/" below and delete "/*1*/".
data tableA;
input Date : ddmmyy10. Sales;
format Date ddmmyy10.;
cards;
29/02/20 10
04/03/20 5
05/03/20 25
06/03/20 30
23/03/20 10
31/03/20 40
16/04/20 40
;
run;
proc print;
run;
data tableB;
input Date : ddmmyy10.;
format Date ddmmyy10.;
cards;
01/02/20
10/02/20
02/03/20
05/03/20
20/03/20
25/03/20
06/04/20
27/04/20
;
run;
proc print;
run;
data tableC;
set tableA tableB;
by Date;
cumulative + Sales;
run;
proc print;
run;
proc sql;
create table tableD as
select distinct c.Date,
max(c.cumulative, (select min(cumulative) from tableC where cumulative > 0)) as CumulativeSales /*1*/
/* c.cumulative as CumulativeSales */ /*2*/
from tableC as c
join
tableB as b
on c.Date = b.Date
;
run;
proc print;
run;
All the best
Bart
Hmmm... other important variable... irrelevant detail.. right?
My remark about comments /*1*/ and /*2*/ is still actual:
data tableA;
input CustID Date : ddmmyy10. Sales;
format Date ddmmyy10.;
cards;
1 29/02/20 10
2 04/03/20 5
1 05/03/20 25
2 06/03/20 30
1 23/03/20 10
2 31/03/20 40
1 16/04/20 40
;
run;
proc sort data = tableA;
by CustID Date;
run;
proc print;
run;
data tableB;
input CustID Date : ddmmyy10.;
format Date ddmmyy10.;
cards;
1 01/02/20
2 10/02/20
1 02/03/20
2 05/03/20
1 20/03/20
2 25/03/20
1 06/04/20
2 27/04/20
;
run;
proc sort data = tableB;
by CustID Date;
run;
proc print;
run;
data tableC;
set tableA tableB;
by CustID Date;
if first.custID then cumulative = 0;
cumulative + Sales;
run;
proc print;
run;
proc sql;
create table tableD as
select distinct c.CustID, c.Date,
max(c.cumulative, (select min(x.cumulative) from tableC as x where c.CustID = x.custID and x.cumulative > 0)) as CumulativeSales /*1*/
/* c.cumulative as CumulativeSales */ /*2*/
from tableC as c
join
tableB as b
on
c.CustID = b.CustID
and
c.Date = b.Date
;
run;
proc print;
run;
All the best
Bart
@Saurabh_Rana wrote:
I have two tables:-
Table(A)= Sales made on each date
Table (A)
Date Sales 29/02/20 10 04/03/20 5 05/03/20 25 06/03/20 30 23/03/20 10 31/03/20 40 16/04/20 40
Table (B)
Date 01/02/20 10/02/20 02/03/20 05/03/20 20/03/20 25/03/20 06/04/20 27/04/20
Now I want to compare dates of table A and dates of table B to create a cumulative sales according to dates in table (B).
This is the required output:-
Date Cumulative Sales 01/02/20 10 10/02/20 10 02/03/20 10 05/03/20 40 20/03/20 70 25/03/20 80 06/04/20 120 27/04/20 160
So basically I need to add all the sales made in table A up to the date mentioned in table B, so that I have cumulative sales up to each date mentioned in table B.
Your first sale in table a is on Feb 29, 2020. So why do you have to show cumulative sales of 10 for the first 2 output records (Feb 1 and Feb 10 of 2020)? Assuming that's an error, then:
data A;
input date : ddmmyy10. sales;
format date ddmmyy10.;
datalines;
29/02/20 10
04/03/20 5
05/03/20 25
06/03/20 30
23/03/20 10
31/03/20 40
16/04/20 40
run;
data B;
input date : ddmmyy10.;
format date ddmmyy10.;
datalines;
01/02/20
10/02/20
02/03/20
05/03/20
20/03/20
25/03/20
06/04/20
27/04/20
run;
data want (drop=sales);
set a (in=ina) b (in=inb);
by date;
if ina then cum_sales+sales;
if inb;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.