Creating a New Column Based on SumIf

[larusso522]

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.

[RW9]

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;

[Reeza]

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

[PaigeMiller]

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

[novinosrin]

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;