BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sufiya
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
Reeza
Super User

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
Quartz | Level 8

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
Super User

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
Super User
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
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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