DATA Step, Macro, Functions and more

how to create grouping/buckets?

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

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-CategoryQuantity
Bookcases2
Chairs3
Labels2
Tables5
Storage2
Furnishings7
Art4
Phones6
Binders3
Appliances5
Tables9
Phones4
Paper3

 

the end result would look like: 

Sub-CategoryBuckets_QuantityQuantity
Bookcases1.Quantity1-52
Chairs1.Quantity1-53
Labels1.Quantity1-52
Tables1.Quantity1-55
Storage1.Quantity1-52
Furnishings2.Quantity6-107
Art1.Quantity1-54
Phones2.Quantity6-106
Binders1.Quantity1-53
Appliances1.Quantity1-55
Tables2.Quantity6-109
Phones1.Quantity1-54
Paper1.Quantity1-53

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. 

View solution in original post


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 Smiley Happy 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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 176 views
  • 0 likes
  • 2 in conversation