BookmarkSubscribeRSS Feed
Question
Fluorite | Level 6

Hi,

 

I woud like to know if there is a way of grouping automatically a variable. I have the table below, customer_id and Total_plays. I would like to group the Total_plays automatically. I usually use proc univariate , then group it manuallly myself.

 

Is there any way to do it automatically, without using manual process.

 

Your help would be much appreciated.

 

Thank you 

 

Have

Customer_id

Total_plays

100000

1

110000

3

120000

1

130000

5

140000

1

150000

2

160000

1

170000

1

180000

1

190000

4

200000

4

210000

3

220000

1

230000

37

 

Want

Customer_id

Total_plays

100000

1

110000

3 to 5

120000

1

130000

3 to 5

140000

1

150000

2

160000

1

170000

1

180000

1

190000

3 to 5

200000

3 to 5

210000

3 to 5

220000

1

230000

> 5

 

5 REPLIES 5
Jagadishkatam
Amethyst | Level 16

Try the format

 

proc format fmtlib;
value cat
low-1='1'
2='2'
3-5='3-5'
6-high='>5';
run;

data have;
input Customer_id Total_plays;
format Total_plays cat.;
cards;
100000 1
110000 3
120000 1
130000 5
140000 1
150000 2
160000 1
170000 1
180000 1
190000 4
200000 4
210000 3
220000 1
230000 37
;
Thanks,
Jag
Question
Fluorite | Level 6
Hi Jagadishkatam ,

Thank you for your response.
I think I haven't expressed myself propery. I meant, is there any way to
group automatically, the group I gave you was an example. I don't have any
predefined groups, I want sas to do it for me.

Hope it makes sense

Thank You

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Jagadishkatam
Amethyst | Level 16
I am not sure how SAS could automatically group the data unless you mention what type of groups you want. So I believe using the format alone is the right approach.

I welcome if there are any other appraoch from the experts.
Thanks,
Jag
mkeintz
PROC Star

You want to group N observations for variable X into G groups, based upon the value of X.  How about proc rank?

 

Let's say you want 4 groups, and you want those groups to be as equal in size as possible.  Then:

 

proc rank data=have out=want group=4;

   var x;

   ranks xgroup;

run;

 

This will produce new variable Xgroup=0,1,2, or 3.  Note that all tied values of X are assigned to the same group, so in the presence of ties, groups need not be the same size.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User

Don't understand you question.

What is the rule to group this variable ?

 


data have;
input Customer_id Total_plays;
format Total_plays cat.;
cards;
100000 1
110000 3
120000 1
130000 5
140000 1
150000 2
160000 1
170000 1
180000 1
190000 4
200000 4
210000 3
220000 1
230000 37
;

proc rank data=have group=5 ties=dense out=want;
var Total_plays;
ranks rank;
run;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2076 views
  • 1 like
  • 4 in conversation