BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tomcaty
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

proc sql;
  create table WANT as
  select CAR,
         sum(SALES) as SALES
  from   HAVE
  group by CAR;
quit;
Tomcaty
Obsidian | Level 7
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
Tourmaline | Level 20

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
Wonderful! this worked. You are really an expert. Many thanks.
PeterClemmensen
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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