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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

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
  • 5 replies
  • 1323 views
  • 0 likes
  • 4 in conversation