DATA Step, Macro, Functions and more

Group and sum observation in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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
Super User
Posts: 7,407

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

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: 5

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: 172

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
Super User
Posts: 7,407

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: 5

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: 

 

Salesfrequencypercent
124024.0%
232032.0%
312012.0%
>=432032%
Total1000100

 

Thank you for all your help!

Solution
‎05-18-2017 12:01 PM
Super User
Super User
Posts: 7,407

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: 5

Re: Group and sum observation in SAS

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 146 views
  • 1 like
  • 4 in conversation