topic Re: How to automatically group a variable in SAS Programming

How to automatically group a variable

Question — Fri, 26 May 2017 15:06:12 GMT

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

Re: How to automatically group a variable

Jagadishkatam — Fri, 26 May 2017 15:17:27 GMT

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
;

Re: How to automatically group a variable

Question — Fri, 26 May 2017 15:39:50 GMT

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. -##

Re: How to automatically group a variable

Jagadishkatam — Fri, 26 May 2017 15:46:45 GMT

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.

Re: How to automatically group a variable

mkeintz — Fri, 26 May 2017 18:12:10 GMT

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.

Re: How to automatically group a variable

Ksharp — Sat, 27 May 2017 15:12:10 GMT

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;