topic Re: Group and sum observation in SAS in SAS Programming

Group and sum observation in SAS

Tomcaty — Thu, 18 May 2017 12:02:29 GMT

Hi all,

I am new to SAS communities. I have a dataset that needs to be grouped as below. How do I group and sum all the observations in that group using SQL statement. Thank you

data have;

input Car sales;

datalines;

1 3

2 4

3 5

;

The output I needed was:

Car sales

1 3

2 4

3 15

I wanted to group and sum up the sales with 3 cars. How do I do this using PRCO SQL.

Thank you,

Much appreciated.

Re: Group and sum observation in SAS

RW9 — Thu, 18 May 2017 12:05:10 GMT

Hi,

proc sql;
  create table WANT as
  select CAR,
         sum(SALES) as SALES
  from   HAVE
  group by CAR;
quit;

Re: Group and sum observation in SAS

PeterClemmensen — Thu, 18 May 2017 12:12:15 GMT

data have;
input Car sales;
datalines;
1 3
2 4
3 5
3 5
3 5
;

data want(drop = Sales);
	set have;
	by Car;
	if first.Car then Sum_Sales = 0;
	Sum_Sales + Sales;
	if last.Car then output;
run;

Re: Group and sum observation in SAS

Tomcaty — Thu, 18 May 2017 12:23:33 GMT

Thanks RW9. This was quick. If car 1 and car 2 sales had multiple observations how do I group only the car 3 sales.

data have;
input Car sales;
datalines;
1 3
1 4
2 4
2 4
3 5
3 5
3 5
;

The output should be:

Car sales
1 3
1 4
2 4
2 4
3 15

Thank you.

Re: Group and sum observation in SAS

novinosrin — Thu, 18 May 2017 12:25:37 GMT

proc sql;
create table WANT as
select CAR,
sum(SALES) as SALES
from HAVE

where car=3 /*add this where clause*/
group by CAR;
quit;

Re: Group and sum observation in SAS

RW9 — Thu, 18 May 2017 12:27:14 GMT

Well, thats a quesiton back to you, how would I, as a computer, know that from this data:

1 3
1 4
2 4
2 4
3 5
3 5
3 5

I should only group 3? No way of telling is there, so you either need to a) add the information to the data so you can tell the computer what to group, or b) do two separate blocks and join them together:
For b):

proc sql;
  create table WANT as
  select *
  from   HAVE
  where CAR ne 3
  union all
  select CAR,
           sum(SALES) as SALES
  from   (select * from HAVE where CAR=3)
  group by CAR;
quit;

As you can see, more code due to the fact the data does not tell the story, so not good.

Re: Group and sum observation in SAS

Tomcaty — Thu, 18 May 2017 15:48:59 GMT

RW9, I apologize for being vague. Heres the actual dataset I have.

data have;
input sales frequency percent;
datalines;
1 240 24.0
2 320 32.0
3 120 12.0
4  80  8.0
5  68  6.8
6  42  4.2
7 130 13.0
;

I wanted to bucket all sales greater than or equal to 4. My output should look something like this:

Sales	frequency	percent
1	240	24.0%
2	320	32.0%
3	120	12.0%
>=4	320	32%
Total	1000	100

Thank you for all your help!

Re: Group and sum observation in SAS

RW9 — Thu, 18 May 2017 15:56:39 GMT

Right, so the key here is to create the variable to group by, as we are only using SQL here, we use a subquery which conditionally creates a character variable of sales (as we have non-numeric in it). Then its simply grouping by that:

data have;
  input sales frequency percent;
datalines;
1 240 24.0
2 320 32.0
3 120 12.0
4  80  8.0
5  68  6.8
6  42  4.2
7 130 13.0
;
run;

proc sql;
  create table WANT as
  select  SALES,
          sum(FREQUENCY) as FREQUENCY,
          sum(PERCENT) as PERCENT
  from    (select case when SALES >= 4 then ">= 4" else strip(put(SALES,best.)) end as SALES,
                  FREQUENCY,
                  PERCENT
           from   HAVE)
  group by SALES;
quit;

Re: Group and sum observation in SAS

Tomcaty — Thu, 18 May 2017 16:02:59 GMT

Wonderful! this worked. You are really an expert. Many thanks.