DATA Step, Macro, Functions and more

Creating a New Column Based on SumIf

Reply
Occasional Contributor
Posts: 10

Creating a New Column Based on SumIf

I have a dataset that looks like this:

 

Year    Quarter    Market    Sales

2017   1              ASL         120

2015   4             DSL           60

2017   1             ASL           70

2015   4              DSL         100

 

I would like to add a column to this summing Sales for each year, quarter, and market. So I need something that looks like this in the end:

 

Year    Quarter    Market    Sales    MarketSize

2017   1              ASL         120       190

2015   4             DSL           60        160

2017   1             ASL           70        190

2015   4              DSL         100       160

 

This is one example of a calculation I need to do. I read that the easiest way to do a sumif on SAS is using proc sql but that would create a new table for each calculation which I would need to merge with my original table. I have dozens of calculations similar to this and it seems inefficient to do so that way. Is there a simpler way to make these calculations? I am a beginner with SAS.

Super User
Super User
Posts: 9,407

Re: Creating a New Column Based on SumIf

Posted in reply to larusso522

Post test data in the form of a datastep!

As such this is not tested:

proc sql;
  create table want as 
  select  a.*,
          (select sum(b.sales) from have b where b.market=a.market group by market) as marketsize
  from    have a;
quit;
Super User
Posts: 23,262

Re: Creating a New Column Based on SumIf

Posted in reply to larusso522

You can do this with a single PROC SQL step or PROC MEANS and a merge. Here's a fully worked example:

 

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

Respected Advisor
Posts: 2,812

Re: Creating a New Column Based on SumIf

Posted in reply to larusso522

Given your example, PROC SUMMARY would work to compute MarketSize, then you could merge the results back into the original table.

--
Paige Miller
PROC Star
Posts: 1,570

Re: Creating a New Column Based on SumIf

Posted in reply to larusso522
data have;
input Year    Quarter    Market   $ Sales;
cards;
2017   1              ASL         120
2015   4             DSL           60
2017   1             ASL           70
2015   4              DSL         100
;

proc sql;
create table want as
select *,sum(sales) as MarketSize
from have
group by year,market;
quit;
Ask a Question
Discussion stats
  • 4 replies
  • 77 views
  • 0 likes
  • 5 in conversation