topic Re: Creating a New Column Based on SumIf in SAS Programming

Creating a New Column Based on SumIf

larusso522 — Wed, 06 Jun 2018 14:45:44 GMT

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.

Re: Creating a New Column Based on SumIf

RW9 — Wed, 06 Jun 2018 14:58:02 GMT

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;

Re: Creating a New Column Based on SumIf

Reeza — Wed, 06 Jun 2018 15:06:22 GMT

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

Re: Creating a New Column Based on SumIf

PaigeMiller — Wed, 06 Jun 2018 15:06:31 GMT

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

Re: Creating a New Column Based on SumIf

novinosrin — Wed, 06 Jun 2018 15:09:22 GMT

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;