## Group and sum observation in SAS

Solved
Occasional Contributor
Posts: 9

# Group and sum observation in SAS

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

3 5

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.

Accepted Solutions
Solution
‎05-18-2017 12:01 PM
Super User
Posts: 8,798

## Re: Group and sum observation in SAS

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;
```

All Replies
Super User
Posts: 8,798

## Re: Group and sum observation in SAS

Hi,

```proc sql;
create table WANT as
select CAR,
sum(SALES) as SALES
from   HAVE
group by CAR;
quit;```
Occasional Contributor
Posts: 9

## Re: Group and sum observation in SAS

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.
PROC Star
Posts: 908

## Re: Group and sum observation in SAS

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;

Super User
Posts: 8,798

## Re: Group and sum observation in SAS

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.

Occasional Contributor
Posts: 9

## Re: Group and sum observation in SAS

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!

Solution
‎05-18-2017 12:01 PM
Super User
Posts: 8,798

## Re: Group and sum observation in SAS

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;
```
Occasional Contributor
Posts: 9

## Re: Group and sum observation in SAS

Wonderful! this worked. You are really an expert. Many thanks.
PROC Star
Posts: 1,097

## Re: Group and sum observation in SAS

``````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;``````
☑ This topic is solved.