HOW TO GROUP BY EVEN FOR THE SEGMENTS WITH NULL VALUES

[rahulsaha2127]

I have a data with Quarter , Product and  SellAmount.  I want to take cumulative sum of sell amount grouped by product and quarter. My data is like this:

Quarter Product Sell_Amount
Q1 P1 220.875
Q1 P2 29.3746
Q1 P3 288.526
Q1 P4 95
Q2 P1 204.85
Q2 P2 235
Q2 P3 820.1037
Q3 P2 583.363
Q3 P3 579.1
Q3 P4 1110.8

There are 3 quarters in data but  I want a grouped table where I want to include quarter 4 as well and force-feed the PRODUCT sell_amount in Quarter 4  equal to ZERO so that the cumulative sum for product sell amount in Q4 is equal to the last quarter (Quarter 3). There can be multiple force-feed quarters like this (Q5,Q6) which doesnot exist in the data l but will be needed for reporting in final pivot and required  output (Cumulative sell amount) will take the same value as the last available quarter for these quarters.

Also not all products are sold in every quarter so I want o adjust my cumulative sum accordingly.

I need a table like this:

Product	Quarter	Sell_Amount	Cumulative_sum
P1	Q1	220.875	220.875
P1	Q2	204.85	425.725
P1	Q3	0	425.725
P1	Q4	0	425.725
P2	Q1	29.3746	29.3746
P2	Q2	235	264.3746
P2	Q3	583.363	847.7376
P2	Q4	0	847.7376
P3	Q1	288.526	288.526
P3	Q2	820.1037	1108.63
P3	Q3	579.1	1687.73
P3	Q4	0	1687.73
P4	Q1	95	95
P4	Q2	0	95
P4	Q3	1110.8	1205.8
P4	Q4	0	1205.8

As highlighted by red in this table there is no data for Q4 but I want to forcefeed information so that all product sell in Q4 equals to zero and the cumulative sum remains the same in Q4 as in Q3. Also there might be some products with no sell in a given quarter but we want to include that product record for the quarter with sell_amount=0 and accordingly create the cumulative sum variable.

Please help with your answers!

[Ksharp · Accepted solution]

data have;
input Quarter $ Product $ Sell_Amount;
cards;
Q1 P1 220.875
Q1 P2 29.3746
Q1 P3 288.526
Q1 P4 95
Q2 P1 204.85
Q2 P2 235
Q2 P3 820.1037
Q3 P2 583.363
Q3 P3 579.1
Q3 P4 1110.8
;

proc sql;
create table temp as
select a.Product,a.Quarter,coalesce(b.Sell_Amount,0) as Sell_Amount from
(select * from
(select distinct Quarter from have union select 'Q4' from have(obs=1)),
(select distinct Product from have)
) as a natural left join have as b 
order by 1,2;
quit;
data want;
 set temp;
 by Product;
 if first.Product then cum=0;
 cum+Sell_Amount;
run;

[rahulsaha2127]

What IF I want to include more quarters and not only 'Q4'? I might want to have Q5, Q6 as well for suppose. What changes need to be done ?
Also Thanks for your response!!

[Ksharp]

OK. Try this one.

data have;
input Quarter $ Product $ Sell_Amount;
cards;
Q1 P1 220.875
Q1 P2 29.3746
Q1 P3 288.526
Q1 P4 95
Q2 P1 204.85
Q2 P2 235
Q2 P3 820.1037
Q3 P2 583.363
Q3 P3 579.1
Q3 P4 1110.8
;

proc sql;
create table temp as
select a.Product,a.Quarter,coalesce(b.Sell_Amount,0) as Sell_Amount from
(select * from
(select distinct Quarter from have
union select 'Q4' from have(obs=1)
union select 'Q5' from have(obs=1)),
(select distinct Product from have)
) as a natural left join have as b 
order by 1,2;
quit;
data want;
 set temp;
 by Product;
 if first.Product then cum=0;
 cum+Sell_Amount;
run;