Solved
Contributor
Posts: 34

# how to create grouping/buckets?

Hello,

Trying to learn more SAS. Could someone please help/explain to me so I can understand - how to create grouping/buckets using the syntax 'case'? what is the logic?

appreciate any help!

example:

Want to take data from table 'product.quantity' and create a new column named 'bucket_quantity" and group quantities from 1-5 in bucket '1.bucket 1-5' and quantities from 6-10 into bucket '2.bucket 6-10'

table 'product.quantity'

 Sub-Category Quantity Bookcases 2 Chairs 3 Labels 2 Tables 5 Storage 2 Furnishings 7 Art 4 Phones 6 Binders 3 Appliances 5 Tables 9 Phones 4 Paper 3

the end result would look like:

 Sub-Category Buckets_Quantity Quantity Bookcases 1.Quantity1-5 2 Chairs 1.Quantity1-5 3 Labels 1.Quantity1-5 2 Tables 1.Quantity1-5 5 Storage 1.Quantity1-5 2 Furnishings 2.Quantity6-10 7 Art 1.Quantity1-5 4 Phones 2.Quantity6-10 6 Binders 1.Quantity1-5 3 Appliances 1.Quantity1-5 5 Tables 2.Quantity6-10 9 Phones 1.Quantity1-5 4 Paper 1.Quantity1-5 3

Accepted Solutions
Solution
‎02-03-2018 06:43 PM
Super User
Posts: 23,293

## Re: how to create grouping/buckets?

You can use PROC SQL to categorize your data. For example, lets assume you were trying to create age groups, the following is what your code would look like.

``````proc sql;

create table want as
select *, case when age < 13 then 'Pre-Teen'
when 14 < age < 16 then 'Teen'
when age > 16 then 'Adult'
else 'Other'
end as Age_Group
from sashelp.class;
quit;``````

This can be run on your installation of SAS.

All Replies
Solution
‎02-03-2018 06:43 PM
Super User
Posts: 23,293

## Re: how to create grouping/buckets?

You can use PROC SQL to categorize your data. For example, lets assume you were trying to create age groups, the following is what your code would look like.

``````proc sql;

create table want as
select *, case when age < 13 then 'Pre-Teen'
when 14 < age < 16 then 'Teen'
when age > 16 then 'Adult'
else 'Other'
end as Age_Group
from sashelp.class;
quit;``````

This can be run on your installation of SAS.

Contributor
Posts: 34

## Re: how to create grouping/buckets?

Thank you Reeza!

ok so when applying the logic you provided - does this make sense?

``````proc sql;

create table NEW_BUCKET as
select *, case when 1 < Quantity < 5 then '1.Quantity1-5'when 6< Quantity > 10 then '2.Quantity6-10'
end as Buckets.Quantity
from product.quantity;
quit;``````

Super User
Posts: 23,293

## Re: how to create grouping/buckets?

Run it and check.

sufiya wrote:

Thank you Reeza!

ok so when applying the logic you provided - does this make sense?

``````proc sql;

create table NEW_BUCKET as
select *, case when 1 < Quantity < 5 then '1.Quantity1-5'when 6< Quantity > 10 then '2.Quantity6-10'
end as Buckets.Quantity
from product.quantity;
quit;``````

Super User
Posts: 23,293

## Re: how to create grouping/buckets?

end as Buckets.Quantity <- this section is wrong, you need to include a variable name, but you don't use the dot notation for new variables. Try just using Quantity.
Contributor
Posts: 34

## Re: how to create grouping/buckets?

ahhh, so for the variable name it shouldn't have the dot or match the table name?

thank you again greatly appreciate the help & guidance!

Contributor
Posts: 34

## Re: how to create grouping/buckets?

I am getting the error message below and don't know what the issue is or why? please help ....

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,

a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

``````proc sql;  create table NEW_BUCKET as

select *, case
when Quantity >=1 and <=5 then '1.Quantity1-5'
when Quantity >=6 and <=10 then '2.Quantity6-10'
when Quantity >=11 then '3.Quantity11+'
end as Buckets

from product.quantity;
quit;``````

Contributor
Posts: 34

## Re: how to create grouping/buckets?

nm, figured it out - it needs to be written this way:

``````proc sql;  create table NEW_BUCKET as

select *, case
when Quantity >1 and Quantity<5 then '1.Quantity1-5'
when Quantity >6 and Quantity<10 then '2.Quantity6-10'
when Quantity >11 then '3.Quantity11+'
end as Buckets

from product.quantity;
quit;``````
☑ This topic is solved.