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 |
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.
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.
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;
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;
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!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: