Group and sum observation in SAS

[Tomcaty]

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.

[RW9]

Hi,

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

[Tomcaty]

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.

[novinosrin]

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;

[RW9]

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.

[Tomcaty]

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!

[RW9 · Accepted solution]

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;

[Tomcaty]

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

[PeterClemmensen]

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;