how to create grouping/buckets?

[sufiya]

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

[Reeza · Accepted solution]

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. 

[sufiya]

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;

[Reeza]

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;

---

[Reeza]

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.

[sufiya]

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! 

[sufiya]

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;

[sufiya]

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;