## Creating a New Column Based on SumIf

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
Posts: 9,407

## Re: Creating a New Column Based on SumIf

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

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

Posts: 2,812

## Re: Creating a New Column Based on SumIf

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

``````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;``````
Discussion stats
• 4 replies
• 77 views
• 0 likes
• 5 in conversation