BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
rahulsaha2127
Fluorite | Level 6

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:

 

ProductQuarterSell_AmountCumulative_sum
P1Q1220.875220.875
P1Q2204.85425.725
P1Q30425.725
P1Q40425.725
P2Q129.374629.3746
P2Q2235264.3746
P2Q3583.363847.7376
P2Q40847.7376
P3Q1288.526288.526
P3Q2820.10371108.63
P3Q3579.11687.73
P3Q401687.73
P4Q19595
P4Q2095
P4Q31110.81205.8
P4Q401205.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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

3 REPLIES 3
Ksharp
Super User
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
Fluorite | Level 6
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
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 922 views
  • 2 likes
  • 2 in conversation