topic Re: how to create grouping/buckets? in SAS Programming

how to create grouping/buckets?

sufiya — Sat, 03 Feb 2018 20:20:03 GMT

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

Re: how to create grouping/buckets?

Reeza — Sat, 03 Feb 2018 20:28:30 GMT

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.

Re: how to create grouping/buckets?

sufiya — Sat, 03 Feb 2018 21:10:36 GMT

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;

Re: how to create grouping/buckets?

Reeza — Sat, 03 Feb 2018 21:29:32 GMT

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;

Re: how to create grouping/buckets?

Reeza — Sat, 03 Feb 2018 21:31:57 GMT

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.

Re: how to create grouping/buckets?

sufiya — Sat, 03 Feb 2018 21:36:09 GMT

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!

Re: how to create grouping/buckets?

sufiya — Sat, 03 Feb 2018 22:41:51 GMT

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;

Re: how to create grouping/buckets?

sufiya — Sat, 03 Feb 2018 23:45:13 GMT

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;