DATA Step, Macro, Functions and more

How to automatically group a variable

Reply
Frequent Contributor
Posts: 96

How to automatically group a variable

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

 

Trusted Advisor
Posts: 1,129

Re: How to automatically group a variable

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
Frequent Contributor
Posts: 96

Re: How to automatically group a variable

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. -##
Trusted Advisor
Posts: 1,129

Re: How to automatically group a variable

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
Valued Guide
Posts: 797

Re: How to automatically group a variable

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.

Super User
Posts: 9,681

Re: How to automatically group a variable

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;
Ask a Question
Discussion stats
  • 5 replies
  • 108 views
  • 0 likes
  • 4 in conversation