topic Re: Compare dates in two tables to derive a cumulative sum in SAS Programming

Compare dates in two tables to derive a cumulative sum

Saurabh_Rana — Sat, 04 Apr 2020 10:28:27 GMT

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.

Re: Compare dates in two tables to derive a cumulative sum

yabwon — Sat, 04 Apr 2020 10:47:48 GMT

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

Re: Compare dates in two tables to derive a cumulative sum

Saurabh_Rana — Sat, 04 Apr 2020 11:44:16 GMT

Actually there is another field customer_no in both the tables along side date, so I need this cumulative sales by customer and by date.

Re: Compare dates in two tables to derive a cumulative sum

yabwon — Sat, 04 Apr 2020 12:03:37 GMT

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

Re: Compare dates in two tables to derive a cumulative sum

mkeintz — Sun, 05 Apr 2020 03:31:36 GMT

@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;