topic Re: Sort and label data in Groups in SAS Programming

Sort and label data in Groups

new_sas_user_4 — Sat, 03 May 2025 01:48:56 GMT

If my table has 1Million records, I want to:

1) sort by largest to smallest by price

2) take the first 100K and call it 'group 1'.....then the next 100K and call it 'group 2' and so on

How can I do it?

proc sort would sort the data

but I am not sure how can I label the records based on the a group of x records.

As an example, I have attached a list of 30 records, and grouped them in 3 creating 10 groups(attached excel)

Re: Sort and label data in Groups

mkeintz — Sat, 03 May 2025 01:59:15 GMT

If you have tied prices, do you have a preferred order among those ties?

Can a set of observations with tied prices be assigned to different (consecutive) groups? Or must tied prices all be in the same group?

Are you using a fixed group size (100,000), or are you aiming for 10 groups of equal size (or as near equal as tied prices might allow)?

Re: Sort and label data in Groups

Tom — Sat, 03 May 2025 03:35:49 GMT

Creating an counter is easy to do with the SUM statement.

Do read 100K per group just put the SET statement inside a do loop.

data want;
  group+1;
  do _n_=1 to 100000;
    set have;
    output;
  end;
run;

But that is probably NOT what you want to do since it could very easily split some tied observations into separate groups.

Did you look at PROC RANK?

Re: Sort and label data in Groups

PaigeMiller — Sat, 03 May 2025 11:45:27 GMT

@new_sas_user

I would also vote for PROC RANK to do this.

What are you going to do with the 10 groups once you have them? Depending on your answer, we may be able to advise further.

PS: most of us will not download Excel files or other Microsoft Office files, as they can be security threats. In the future, if you want to show us something, show us what you want as text in your message (not as an attachment). If you want to show us a graphic, paste the graphic into your message (do not include it in an attachment).

Re: Sort and label data in Groups

Ksharp — Sun, 04 May 2025 01:40:44 GMT

data want;
 set have;
if mod(_n_,10000)=1 then group+1;
run;

Re: Sort and label data in Groups

mkeintz — Mon, 05 May 2025 03:34:39 GMT

Let's say you want exactly 10 equal size groups, subject to

you don't know exactly how many obs are in the dataset
you may have tied price values
you want to keep tied prices in the same group:

Program below is corrected, by entering an explicit OUTPUT statement, which prevents premature increment to the GROUP variable.

proc sort data=have out=need;
  by descending price;
run;
data want;
  set need nobs=n_need;
  by descending price;
  retain group 1;
  output;
  if last.price=1 and _n_ > group*(n_need/10) then group+1;
run;

Re: Sort and label data in Groups

new_sas_user_4 — Mon, 05 May 2025 02:16:19 GMT

HAVE		WANT
sub_no	price	label	sub_no	price
abc	10	group 1	az	140
efg	15	group 1	ay	136
hij	88	group 1	ax	132
lmn	20	group2	af	128
opq	26	group2	ae	124
abc	113	group2	ad	121
ac	117	group 3	ac	117
ad	121	group 3	abc	113
ae	124	group 3	ar	109
af	128	group 4	aq	105
ag	67	group 4	ap	102
ah	71	group 4	av	100
ai	75	group 5	ao	98
aj	79	group 5	an	94
ak	82	group 5	am	90
al	86	group 6	hij	88
am	90	group 6	al	86
an	94	group 6	ak	82
ao	98	group 7	aj	79
ap	102	group 7	at	78
aq	105	group 7	ai	75
ar	109	group 8	ah	71
as	59	group 8	ag	67
at	78	group 8	as	59
au	59	group 9	au	59
av	100	group 9	opq	26
aw	7	group 9	lmn	20
ax	132	group 10	efg	15
ay	136	group 10	abc	10
az	140	group 10	aw	7

Ideally I want group 1 (example) to have no overlap with the price of group 2. but if there is an overlap in price, then in that case I would want to have that price grouped under group 1 ....so essentially the price is tied to the group.

Re: Sort and label data in Groups

Ksharp — Mon, 05 May 2025 09:41:43 GMT

Then try Paige 's suggestion PROC RANK + " ties=dense " option.

Re: Sort and label data in Groups

PaigeMiller — Mon, 05 May 2025 12:47:36 GMT

@new_sas_user_4

Please answer my earlier question: "What are you going to do with the 10 groups once you have them? Depending on your answer, we may be able to advise further."

A suggestion: present data as text, not as a copy/paste from Excel or elsewhere. Ideally present data as working SAS data step code (examples and instructions). Your table has a lot of white space, and is too long vertically for us to see all of it at once without scrolling up and down, so it is very hard to grasp any understanding from your table of data.

Re: Sort and label data in Groups

new_sas_user_4 — Tue, 06 May 2025 15:04:15 GMT

The solution provided worked 🙂 thanks @mkeintz

I confirmed that I really just need to sort by price and then divide the volume of rows by 10 🙂 so below is what worked (modified @mkeintz 's query)

and it would be my final output.

proc sort data=have out=need;
by descending price;
run;
data want;
set need nobs=n_need;
by descending price;
retain group 1;
output;
if _n_ > group*(n_need/10) then group+1;
run;

Thanks everyone for helping out 🙂