topic Re: Looking for a better way in SAS Programming

Looking for a better way

buechler66 — Tue, 28 Aug 2018 14:10:18 GMT

Hi. I need the individual all_volume for each region. Currently I run this in 3 different data steps (see below). I'm wondering if there's a Proc I could us instead? And if you could help with the syntax for it? Thanks.

proc sql;
create table Want as
select sum(a_volume+b_volume+c_volume) AS all_volume
from Have
where region = '1'
;quit;

proc sql;
create table Want as
select sum(a_volume+b_volume+c_volume) AS all_volume
from Have
where region = '2'
;quit;

proc sql;
create table Want as
select sum(a_volume+b_volume+c_volume) AS all_volume
from Have
where region = '3'
;quit;

Re: Looking for a better way

PeterClemmensen — Tue, 28 Aug 2018 14:13:43 GMT

So you want a separate data set for each unique value of region, correct? There are several ways to do this, but my first question is: Why?

It is rarely a good idea to split a data set like this. Instead use By-Group processing on the original data set.

Re: Looking for a better way

RW9 — Tue, 28 Aug 2018 14:16:56 GMT

Do not split data. SAS is based on the by group principal. One dataset with groups of data, so:

proc sql;
  create table want as
  select  region,
          sum(a_volume+b_volume+c_volume) as all_volume
  from    have
  group by region;
quit;

Re: Looking for a better way

novinosrin — Tue, 28 Aug 2018 14:26:00 GMT

@PeterClemmensen is right IMHO.

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

Re: Looking for a better way

buechler66 — Tue, 28 Aug 2018 14:34:34 GMT

Thanks so much for your time.

Re: Looking for a better way

buechler66 — Tue, 28 Aug 2018 14:35:31 GMT

No, sorry I wasn't more clear on that. I'm just plugging them into a spreadsheet so a single output is ideal. Sorry again.